Finding numbers with a specific length from csv file
I'm working with a csv file from a customer, which holds a large amount of data. The data is extracted from an SQL database and the commas therefore signify the different columns. In one of these columns there are 10 digit numbers. For some reason all 10 digit numbers starting with 0 have been converted to 9 digit numbers with the 0 removed. I need to find all these instances and insert a 0 at the beginning of the 9 digit number.
A complication in the data is that another column also contains 9 digit numbers, and these do not need to be modified. I can assume, however that all those numbers start with 0 and all the numbers i need to find do not start with 0.
I'm currently using notepad++ trying to fix the problem and found the regular expression \d{9} which finds all numbers with 9 digits, but that is not what I'm looking for
Below i have an example of how the data could look. The column that needs all 9 digit numbers converted is on the left, and the other column with 9 digit numbers is on the right. An example of the data that is causing the trouble could be:
| Column 1 | Column 2 |
|---|---|
| 2323232323 | 002132413 |
| 231985313 | 004542435 |
In this example I need to find the second line of column 1 and insert a 0 in front of the number.
- Ctrl+H
- Find what:
\b(?!0)\d{9}\b - Replace with:
0$0 - TICK Wrap around
- SELECT Regular expression
- Replace all
Explanation:
\b # word boundary, make sure ae haven't digit before
(?!0) # negative lookahead, make sure the next character is not 0
\d{9} # 9 digits
\b # word boundary, make sure ae haven't digit after
Replacement:
0 # 0 to be inserted
$0 # the whole match (i.e. 9 digts)
Screenshot (before):
Screenshot (after):
- Pandas Error: need to escape, but no escapechar set
- How to stream DataFrame using FastAPI without saving the data to csv file?
- How can decimal values be totalled in a Windows batch file for loop
- Laravel validator and excel files error
- Parse very large CSV files with C++
- Java - Having a huge ArrayList (1 million +), how to create a String of it in a acceptable amount of time?
- convert a C# datetime object into Excel date format
- Sort CSV file based on first column
- Reading csv file converts it to json but it takes every row in it as a string in karate
- Is there is Limition of using opencsv API or Apache Poi Api?
- How to convert CSV or Object-Array to an object but with the first column as properties
- Python | Pyodbc | Encoding FetchAll() to Utf-8 with .Encode('utf-8')
- Python: Split CSV with character count
- how to download a csv generated from papaparse?
- csv.reader() is separating values by individual character
- Read csv value in loop controller Jmeter
- How to delete columns in a CSV file?
- csv.Error: iterator should return strings, not bytes
- How to correctly read a CSV file while escaping delimiter comma placed within square brackets using Apache Spark and Scala?
- Parse a tab-delimited, multiline text into an array of associative arrays using predefined column names
- sqlite3 import csv with column and row separator
- Convert every two values in a comma-separated string into associative array elements
- How can I separate tokens when there are some null tokens
- Writing dictionary items in csv correct format
- Handling empty cells and using null in Examples from a CSV file
- How to concatenate text from multiple rows into a single text string in SQL Server
- Retrieve whole lyrics from URL
- Export table to file with column headers (column names) using the bcp utility and SQL Server 2008
- read csv file delimited by file separator control character (FS/ASCII 28/UTF-8 0x1C) into duckdb using the CLI
- Efficiently filtering comma separated strings in pandas/dask