Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2009
    Location
    INDIA
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Exporting CSV file using Excel 2007

    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!!
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    Last edited by RetiredGeek; 2011-05-11 at 09:27.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    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/ma...0.aspx?redir=0

    Tom Duthie

  4. #4
    Lounger
    Join Date
    Mar 2009
    Location
    INDIA
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Unhappy

    Quote Originally Posted by duthiet View Post
    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/ma...0.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 !!

  5. #5
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    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.
    Last edited by pjustice57; 2011-05-12 at 09:48.
    PJ in FL

  6. #6
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    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)
    Attached Files Attached Files
    PJ in FL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •