C M
asked on
Convert a column format in Excel to csv so that I can load into another application
Hi Experts,
I would like to convert a column formatted excel spreadsheet into a csv format. See attached example of current version.
However I need it to include comma's
E.g.
"Number","Name","ColumnA",
"1","TestImport1","Major",
"2","etc","etc","etc"
I guess there are ways to do this e.g. by ETL using software like Talend. But can excel do this automatically or a simple quick way to do this?
Test-Import.xlsx
I would like to convert a column formatted excel spreadsheet into a csv format. See attached example of current version.
However I need it to include comma's
E.g.
"Number","Name","ColumnA",
"1","TestImport1","Major",
"2","etc","etc","etc"
I guess there are ways to do this e.g. by ETL using software like Talend. But can excel do this automatically or a simple quick way to do this?
Test-Import.xlsx
Qlemo
Save As should allow you to provide the desired format. Excel uses the format defined by your OS settings by default - e.g. US uses commas.
ASKER
Hi Qlemo, thanks for the quick response. Sorry I don't use excel at all so not sure how to save so that double quotation marks encapsulate the column data and then a comma to separate the column data. At moment when I save as csv it appears as below but I need it to appear as the example above:
Number;Name;A;B
1;Testing Risk Import1;Major;Likely
2;Testing Risk Import2;Minor;Possible
3;Testing Risk Import3;Moderate;Rare
Can you please let me know how I can configure. I am technical but unsure where or how to do this in excel.
Many thanks,
C
Many thanks,
C
Number;Name;A;B
1;Testing Risk Import1;Major;Likely
2;Testing Risk Import2;Minor;Possible
3;Testing Risk Import3;Moderate;Rare
Can you please let me know how I can configure. I am technical but unsure where or how to do this in excel.
Many thanks,
C
Many thanks,
C
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi C,
follow this steps:
1. Save your files in Excel as csv.
2. Open the saved csv file with MS Editor or so called Notepad Start Menu Windows
3. In Notepad, click Edit on the menu bar, then select Replace in the Edit menu.
Once in the search and replace window enter the text you want to find and the text you want to use as a replacement.
Menu > Search > Change ; to ,
4. Save
Microsoft Notepad is included with all versions of Windows and can be used to replace text in plain text files. To replace text follow the steps below.
Regards,
Adam
follow this steps:
1. Save your files in Excel as csv.
2. Open the saved csv file with MS Editor or so called Notepad Start Menu Windows
3. In Notepad, click Edit on the menu bar, then select Replace in the Edit menu.
Once in the search and replace window enter the text you want to find and the text you want to use as a replacement.
Menu > Search > Change ; to ,4. Save
Microsoft Notepad is included with all versions of Windows and can be used to replace text in plain text files. To replace text follow the steps below.
Regards,
Adam
If you consider a two-steps approach: Export as you are used to, then run a PowerShell script to convert to the "correct" format. That is cumbersome, however.
Better: Use a PowerShell script to do the work for you. But in fact that is not different to the Excel VBA procedure suggested above by Wayne, as long as you do not want batch processing.
Better: Use a PowerShell script to do the work for you. But in fact that is not different to the Excel VBA procedure suggested above by Wayne, as long as you do not want batch processing.
On a separate sheet create simple formula links to the cells on the data sheet, eg:
=""""&Sheet1!A1&""""
The multiple sets of " is so that you get double quotes around the result of the formula.
Copy across and down until all cells represented on separate sheet.
Using the file Save As menu then save the file as CSV. There will be an error message saying that csv format does not support multiple sheets, ok through that and any other messages.
Open the csv file in notepad and you will find that you have the values enclosed in double quotes and separated by commas. When I did it I ended up with multiple double quotes around each value, 3 sets at either end. This can be corrected with a simple Find and Replace:
Find: """
Replace: "
Thanks
Rob H
=""""&Sheet1!A1&""""
The multiple sets of " is so that you get double quotes around the result of the formula.
Copy across and down until all cells represented on separate sheet.
Using the file Save As menu then save the file as CSV. There will be an error message saying that csv format does not support multiple sheets, ok through that and any other messages.
Open the csv file in notepad and you will find that you have the values enclosed in double quotes and separated by commas. When I did it I ended up with multiple double quotes around each value, 3 sets at either end. This can be corrected with a simple Find and Replace:
Find: """
Replace: "
Thanks
Rob H
ASKER
Works perfect thanks