Pipe "|" Delimiter CSV?

  • Hi All


    Got a really tricky one... Can a excel sheet be saved as CSV but with Pipe "|" delimiter instead of ","?


    I know there is a way to change windows wide delimiters from "," to "|", but that is not ideal, is there a way for excel VBA code to do it?


    Or alternatively, VBA code that changes the windows value, saves a CSV, and then changes back the windows value to a ","?


    Thanks in advance.

  • Re: Pipe "|" Delimiter CSV?


    You can't 'natively' save a file using delimiters other than a comma, tab or space.


    You can change your computer settings to use '|' as the text delimiter.


    Access can be used to export a table (as a linked table) using the '|' as the delimiter, although you may not have a copy of Access to hand. You can use ADO/DAO to open an Excel file as a database and export that but that means more code and more complication and the worksheet must be a simple list like a database.


    As infomage said (posts overlapped), you can save as a 'standard' CSV and edit the file. This can also be automated - again more code and more complication.


    The only other alternative is to use VBA to write to a file using '|' as the field delimiter



    (Quick & Dirty)

  • Re: Pipe "|" Delimiter CSV?


    Hardly... I labeled it 'Quick & Dirty' :) but Thank you.


    I will make 1 change. The lines

    Code
    1. For j = 1 To UsedColumns - 1
    2. Print #1, .Cells(i, j); "|";
    3. Next j
    4. Print #1, .Cells(i, UsedColumns)


    Writes the values of the cells, but you can use

    Code
    1. For j = 1 To UsedColumns - 1
    2. Print #1, .Cells(i, j).Text; "|";
    3. Next j
    4. Print #1, .Cells(i, UsedColumns).Text


    to write the formatted (as displayed) values rather than the underlying values.

  • Re: Pipe "|" Delimiter CSV?


    Thank you again, then.


    Just to finish off your post, a generic procedure to replace all occurrences of 1 string with another in a file.


    Instead of just replacing delimiters it can also be used to replace all occurrences of 'Ford' (for example) with 'Chrysler' - although 'Chrysler Mustang' does not roll off the tongue too easily!



    Optionally, it creates a sequentially numbered backup of the original file.

  • Re: Pipe "|" Delimiter CSV?




    Thanks Cytop, this looks the best solution.


    Can you run me through the code so I can best implement it into my code:


    Code
    1. '// Define a suitable file name
    2. Open "C:\temp\pipedexport.txt" For Output As #1


    They need it in a ".csv" file, can I edit this file name code to generate dynamically something like:


    Code
    1. Open ScoutSavePathStr & "\Scout Upload " & RefNoStr & " - " & CurrentDateStr & ".csv" For Output As #1


    Or do I need to actually generate this file before the above? Basically currently I have all the data in a sheet called "Scout" (which is the system its uploading to), need to get all the data in that sheet into a piped CSV file.


    Code
    1. For i = 1 To UsedRows
    2. For j = 1 To UsedColumns - 1
    3. Print #1, .Cells(i, j); "|";
    4. Next j
    5. Print #1, .Cells(i, UsedColumns)
    6. Next i
    7. End With


    From what I understand, this loop is simply going through all used cells in the sheet and printing the contents + "|" into the text file (the output file). Will this work as is with a csv file?



    EDIT: Just re-reading the instructions from the Scout people (who are making this a pain in the ass lol), here's there vague instructions:


    Quote

    1. Fill in the data in one cell, use | as delimiter and remember to enter ~ as Line End Indicator of each line.
    2. Save the file as Comma delimited CSV
    3. Send email to "[email protected]"domain.com with the CSV as attachment, Subject: Reference


    Couldn't the above code work straight in excel without the print to output? Just do the above in A1?


    Thanks :)

  • Re: Pipe "|" Delimiter CSV?


    Quote

    can I edit this file name code to generate dynamically something like...


    Yes, I just used a simple fixed file name as an example. Just be aware that any existing file with the same name will be truncated (effectively overwritten, without warning) as soon as you open the file.


    Quote

    Will this work as is with a csv file


    No problem, you just name the file with any extension you want (Even .XLSM). when writing to a file, the extension you use doesn't really matter as long as the program reading the file can understand the structure.


    Quote

    this loop is simply going through all used cells in the sheet and printing the contents + "|" into the text file


    Yep - nothing complicated there.


    Quote

    remember to enter ~ as Line End Indicator of each line


    Code
    1. For i = 1 To UsedRows
    2. For j = 1 To UsedColumns - 1
    3. Print #1, .Cells(i, j); "|";
    4. Next j
    5. Print #1, .Cells(i, UsedColumns) & "~"
    6. Next i


    One thing not clear is if a New Line character is added after that. If it is then no problem but if the other program interprets '~' as the end of the line then it will need a ";" as well

    Code
    1. Print #1, .Cells(i, UsedColumns) & "~";


    Note the semicolon is outside the double quotes.


    You can"Fill in the data in one cell" if you wish and saving that as a CSV file will work as there's only one cell to export so no field delimiters (',') are needed. Getting all the information into 1 cell, adding the pipe delimiters and the ~ line terminator would be a pain, though.

  • Re: Pipe "|" Delimiter CSV?


    Quote

    No problem, you just name the file with any extension you want (Even .XLSM). when writing to a file, the extension you use doesn't really matter as long as the program reading the file can understand the structure.


    So would I not need to create this "csv" file first? Would simply having it as the "open "blah.csv" For output as #1" work?


    The "~" is already in the last column of the work sheet, so I believe your code will automatically include it correctly.


    Could you explain why doing it within excel (into A1) would be a pain? Wouldn't it just be something like:


    Code
    1. For j = 1 to UsedColumns - 1
    2. .Range("A1").Value = .Range("A1").Value & .Cells(i,j).Text & "|"
    3. Next j


    Wouldn't that work? Or is there something I'm missing.

  • Re: Pipe "|" Delimiter CSV?


    Opening a file 'For Output' will create the file if it doesn't exist, or truncate any existing file.


    Quote

    Wouldn't that work...


    Yes. You can do that if you wish, but there is no particular advantage doing it that way.


    Quote

    The "~" is already in the last column


    Just covering all bases. You suddenly mention something that is non-standard for a CSV file so tried to cover that. If the character is already in the cells then it will be included.

  • Re: Pipe "|" Delimiter CSV?


    Looks like the requirements are broken...


    I wonder if they are seeking a format similar to:


    Val1|Val2|Val3~,Val4|Val5|Val6~,Val7|Val8|Val9~


    to represent:


    Val1 Val2 Val3
    Val4 Val5 Val6
    Val7 Val8 Val9


    I recommend seeking clarity though - perhaps worth asking whether that have a sample file?

  • Re: Pipe "|" Delimiter CSV?



    I just coded it to do it within excel, but the loop is fairly slow, takes a minute or two on big files, will try your print to file way as well and see which is quicker.