Exporting CSV file using Excel 2007

Topic

New Reply

Hello All,

I am using Excel 2007 and need to modify a CSV file so that it can used back with other applications. (Currently, I am exporting it to be used for Documentum).

When I edit and save the file, Excel automatically adds 2 extra semi-colons at the end of every code line. ( I checked this by opening the file in Notepad)

Just to share, with Excel 2003, no addition is made.

For reference, I am attaching two files depicting how the file should look vs how it becomes after it is saved using Excel 2007.

Due to these extra semi-colons, my application is not accepting this updated file.
Can anyone please help on this !!

Thanks in advance!!

Reply | Quote

Replies

In my tests Excel 2007 doesn’t seem to be adding 2 extra delimiters but rather making sure that each row has the exact number of columns {delimiters} as the row in the Excel workbook with the most columns used. In this case 4 columns.

When I tested it on 2003 the only difference was that empty rows did not have the 3 delimiters {4 columns} they were blank.

Sorry but I can’t find a way to change this behavior.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

There is a addin on the MS site called CSV File Creator, don’t konw if will help, but its woth a try.
http://office.microsoft.com/en-us/marketplace/csv-file-creator-for-excel-EM010277030.aspx?redir=0

Tom Duthie

Reply | Quote

There is a addin on the MS site called CSV File Creator, don’t konw if will help, but its woth a try.
http://office.microsoft.com/en-us/marketplace/csv-file-creator-for-excel-EM010277030.aspx?redir=0

Tom Duthie

Thanks, Tom for the pointer !!

This is exactly what I am looking for, but I will need to buy it to get the full functionality.

This trial version does eliminate the deliminator issue but does not accept any modified file.
Meaning, if I modify the CSV file before using this Add-in, it gives an error

Probably will have to search more on this ….

thanks again !!

Reply | Quote

Actually there are three inconsistencies:

1. Your “correct” file contains an extra line “sep=;”
2. All lines end in only the linefeed character LF (0x0A), instead of the “normal” CRLF (0x0D 0x0A) characters.
3. Some lines have only a single “;” and some lines are completely blank. What causes each situation?

Are these differences expected and necessary? Without access to the original input .CSV file, I’m unable to determine what differences are significant and what differences are editor errors.

Reply | Quote

In lieu of an Excel solution, run this .CMD file against the Excel output to remove the extra “;” characters.

Note: It doesn’t correct the other issues I outlined in the previous post. If you need that level of customization, I’d suggest writing an Excel macro to write the CSV file line by line from the data on the spreadsheet.

CMD file to change “;;” and “;;;” to “;”

Code:

@echo off
::FixCSV – parses input file line by line and replaces “;;” and “;;;” with “;”
::syntax: FixCSV infile outfile
::          infile   – file to be parsed
::			outfile	 – output file
::NOTE: Enclose infile and outfile in quotes if there are any spaces in the filenames
::derived from BatchSubstitute.bat at http://www.dostips.com/DtCodeBatchFiles.php#Batch.FindAndReplace

SETLOCAL ENABLEEXTENSIONS
SETLOCAL DISABLEDELAYEDEXPANSION

if exist %2 del %2

:: replace “;;;” with “;”
for /f “tokens=1,* delims=]” %%A in (‘”type %1|find /n /v “””‘) do (
    set “line=%%B”
    if defined line (
        call set “line=echo.%%line:;;;=;%%”
        for /f “delims=” %%X in (‘”echo.”%%line%%””‘) do %%~X >> zzTemp.out
    ) ELSE echo.
)

:: replace “;;” with “;”
for /f “tokens=1,* delims=]” %%A in (‘”type zzTemp.out|find /n /v “””‘) do (
    set “line=%%B”
    if defined line (
        call set “line=echo.%%line:;;=;%%”
        for /f “delims=” %%X in (‘”echo.”%%line%%””‘) do %%~X >> %2
    ) ELSE echo.
)

del zzTemp.out

Download the file here (contains the .CMD file)

Reply | Quote