If you are working on Data migration most of time the clients will provide the data in Excel Spreadsheets. You can convert XLS/XLSX files directly to CSV using Save as option. CSV are great data format for data processing or loading to database tables using tools like SQL Load.
The main issue arises when Excel spreadsheet contains commas in the fields , like in an address where the each word is suffixed with a comma. Although Excel saves these fields included in double quotes , it’s still messy when loading to SQL Load and you will have to use additional clause to specify that fields maybe optionally enclosed with double quotes .
The solution is to use uncommon character as a delimiter instead of comma, The Pipe (“|”) is a great choice. But if you save the file in CSV then you need to open the file in a Text editor and replace all commas with pipe symbol. Also you need to be careful that you will not replace any commas within the data field values.
Instead of doing that you can simply save the Excel spreadsheet directly to Pipe delimited file by following below instructions.
1. Start—> Control Panel
2. Click the Clock, Language and Region option
3. Select Region and Language Option
4. Click Additional Settings
5. Replace List Separator field value from comma to pipe
6. Press Ok and exit .
7. Open the Excel Spreadsheet and Save as CSV.
It’s that simple.
0 comments:
Post a Comment