Procedure to export a text file with both comma and quote delimiters in Excel

Pavan.Sada.PS · May 26, 2015

Hello Friends,

I have a file which has fixed 101 columns and variant 2lakh rows which has to be exported to a text file with both comma and quote delimiters. I am using
https://support.microsoft.com/en-us/kb/291296/en-us

I takes around 4 - 5 hours to complete the export process and my half a day becomes non productive until it gets completed. Just wanted to check if there is any alternative option to export the data into a CSV file with those two delimiters.

Regards,
Pavan S

Hui · May 26, 2015

Can you please post a small sample file with say 10 rows

Marc L · May 26, 2015

Hi,

the computer is a very slow one ?!

Differents ways to achieve that, it depends on files too,
so if you join a data source .xlsx workbook (w/o code) and a desired csv file,
it will be easier to think about it …

Hui · May 26, 2015

the code is slow as it is accessing the worksheet for every cell in the range of101 x 100000 = 10,100,000 cells

I have re-written the code to load the data once (Which may crash on this amount of data)

Lets see how this goes

Pavan.Sada.PS · May 26, 2015

Hello Hui,

Attached is the sample data of 10 Rows.. I have tested your code with small data say 1000 rows it did well.. let me make a try with real data of 2 lakh rows and will get you the output.

@Marc L : I have got the Brand new TP just to run this process which has i7 Processor with 8 GB RAM.. but still..

Output file: Consumption Brio Load

Marc L · May 26, 2015

Maybe you have Excel 2013 …

But try this demonstration :

Code:

Sub Demo1()
    T! = Timer

    With Sheet1.Cells(1).CurrentRegion.Rows
        ReDim S$(1 To .Count)

        For R& = 1 To .Count
          S(R) = """" & Join(Application.Index(.Item(R).Resize(2).Value, 1), """,""") & """"
        Next
    End With

    R = FreeFile
    Open ThisWorkbook.Path & "\Export.csv" For Output As #R
    Print #R, Join(S, vbNewLine)
    Close #R
    MsgBox "Done in " & Format(Timer - T, "0.000s !"), vbExclamation, "  Export"
End Sub

Do you like it ? So thanks to click on bottom right Like !

Pavan.Sada.PS · May 26, 2015

Marc L said:

Maybe you have Excel 2013 …

But try this demonstration :

Code:

Sub Demo1()
    T! = Timer
 
    With Sheet1.Cells(1).CurrentRegion.Rows
        ReDim S$(1 To .Count)
 
        For R& = 1 To .Count
          S(R) = """" & Join(Application.Index(.Item(R).Resize(2).Value, 1), """,""") & """"
        Next
    End With
 
    R = FreeFile
    Open ThisWorkbook.Path & "\Export.csv" For Output As #R
    Print #R, Join(S, vbNewLine)
    Close #R
    MsgBox "Done in " & Format(Timer - T, "0.000s !"), vbExclamation, "  Export"
End Sub

Do you like it ? So thanks to click on bottom right Like !

Hello Marc,

Thanks for the code.. I am using Excel 2010.. Once I Execute the code I get a Run time error 14 at Print #R, Join(S, vbNewLine).

I hope this is much faster but having this error as the String variable cant hold that much data..

Pavan.Sada.PS · May 26, 2015

Hello Hui,

When I execute the macro code I get the run time error 7 and it reads as out of memory at line myArr = Selection.Value2.

Regards,
Pavan S

Marc L · May 27, 2015

Both codes from Hui and I work with your sample workbook,
I guess you do not test them with it !

You have not enough memory, so try this new demonstration !

Code:

Sub Demo2()
    T! = Timer
    FF% = FreeFile
    Open ThisWorkbook.Path & "\Export.csv" For Output As #FF

    With Sheet1.Cells(1).CurrentRegion.Rows
        For R& = 1 To .Count
            Print #FF, """" & Join(Application.Index(.Item(R).Resize(2).Value, 1), """,""") & """"
        Next
    End With

    Close #FF
    MsgBox "Done in " & Format(Timer - T, "0.000s !"), vbExclamation, "  Export"
End Sub

Post the procedure time necessary to create the text file.

And thanks to do not forget to click on bottom right Like !

Pavan.Sada.PS · May 27, 2015

Hello Marc,

This is super cool and does it in 40 Sec for 1.6 Lakh lines. Can u help me understand on what "!" and "%" and Application.Index(.Item(R).Resize(2).Value do?

Regards,
Pavan S

Marc L · May 27, 2015

Characters are like Dim statement, see for example Integer VBA help
and by running code in step by step mode (hit F8 key),
just check types in Local variables window …

Index is the worksheet function (see Excel help), extracts data
to a one-dimensional array, Resize sets the range size …

So instead of writing data cell by cell to text file like the Microsoft
gas factory code, better (♪ faster, ♫ smarter) is to write entire row at once !

My code was developed with same Excel 2003 version of Microsoft code !

Marc L · May 28, 2015

Try this first demonstration amended version :

Code:

Sub Demo1a()
T! = Timer

With Sheet1.Cells(1).CurrentRegion.Rows
    For R& = 1 To .Count
        S$ = S$ & """" & Join(Application.Index(.Item(R).Resize(2).Value, 1), """,""") & """" & vbNewLine
    Next
End With

R = FreeFile
Open ThisWorkbook.Path & "\Export.csv" For Output As #R:  Print #R, S;:  Close #R
MsgBox "Done in " & Format(Timer - T, "0.000s !"), vbExclamation, "  Export"
End Sub

Tell me if it works, how much time …

If time is too close to Demo2, stay with Demo2 …

Pavan.Sada.PS · May 29, 2015

Marc L said:
Try this first demonstration amended version :

Code:

Sub Demo1a() T! = Timer With Sheet1.Cells(1).CurrentRegion.Rows For R& = 1 To .Count S$ = S$ & """" & Join(Application.Index(.Item(R).Resize(2).Value, 1), """,""") & """" & vbNewLine Next End With R = FreeFile Open ThisWorkbook.Path & "\Export.csv" For Output As #R: Print #R, S;: Close #R MsgBox "Done in " & Format(Timer - T, "0.000s !"), vbExclamation, " Export" End Sub

Tell me if it works, how much time …

If time is too close to Demo2, stay with Demo2 …

Sure Marc will let you know..

Pavan.Sada.PS · Jun 2, 2015

Hello Marc,

There is a huge difference.. Amended version took 30 Min against the Demo2 which took 50 Sec for 234339 rows

Regards,
Pavan S

Marc L · Jun 3, 2015

Wow ! Good to know that, thanks for your return !

Marc L · Jun 5, 2015

Hello,

you may save some few seconds with this new demonstration
(Windows only) :

Code:

Sub Demo3()
    Dim Rg As Range
        T! = Timer
    Set Rg = Sheet1.Cells(1).CurrentRegion.Rows

    With CreateObject("ADODB.Stream")
        .Charset = "Windows-1252"
        .Open

        For R& = 1 To Rg.Count
            .WriteText """" & Join(Application.Index(Rg.Item(R).Resize(2).Value, 1), """,""") & """", 1
        Next

        .SaveToFile ThisWorkbook.Path & "\Export.csv", 2
        .Close
    End With

    Set Rg = Nothing
    MsgBox "Done in " & Format(Timer - T, "0.000s !"), vbExclamation, "  Export Demo3"
End Sub

If you like it, so thanks to …

I guess time : 45 seconds, under ?

Pavan.Sada.PS · Jun 8, 2015

Hello Marc,

Let me try this new Demo and get back to you with the answer.

Regards,
Pavan S

Pavan.Sada.PS · Jun 9, 2015

Hello Marc,

Demo 3 did the same in 44.72 sec for 247338 rows

Marc L · Jun 9, 2015

Yes ‼ Demo3 is faster than Demo2 (50 Sec for 234339 rows) !

The Windows ADODB stream method is worth for thousands rows
but for smaller files, classic way of Demo2 is quick enough …

Have a good use !

Jerry Hall · Dec 22, 2015

Thanks for this. I saw 74cols 115k rows in under 8 second. Nice!

Jerry Hall · Dec 22, 2015

After uploading the file created I noticed the first column isn't quoted. When I uploaded the file to my Drive account an imported into CartoDB it put the entire string in a single column shown below. I changed the comma to a semi-colon as well as when I download CartoDB backups they use the semi-colon (not sure if tha matters).

To test I manually concatenated the first column eg "500000" but, the result after running the script was this (abbreviated): 500000"";"33.72310971";"-116.2211431";"455.02";

Thanks if anyone can help!

This is the full row described above:
500000;"33.72310971";"-116.2211431";"455.02";"455.02";"20";"100196";"LIC";"SUREND";"1/21/1981";"6/30/2016";"P40";"";"Y";"12";"3309";"8";"MEZA ALFREDO T";"82675 INDIO BLVD";"";"INDIO";"CA";"92201";"RIVERSIDE";"";"";"";"";"";"";"";"";"";"19364501";"";"";"";"";"9/9/2015";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"<a href="http://www.abc.ca.gov/datport/LQSdata.asp?ID=19364501" target="_blank>100196</a>";"1441774800"

Marc L · Dec 22, 2015

Hi Jerry !

As any of my codes quote every column from source range
so I can't reproduce your issue, better is you attach a xlsx source workbook
and post between tags your code …

Jerry Hall · Dec 22, 2015

Marc L said:
Hi Jerry !

As any of my codes quote every column from source range
so I can't reproduce your issue, better is you attach a xlsx source workbook
and post between tags your code …

Heh, here I thought I was special - sorry about that

Here's a sample of the code from Excel before applying the macro. Thanks for your help!

Here's the code I used:

Code:

Sub Demo3()
  Dim Rg As Range
  T! = Timer
  Set Rg = Sheet1.Cells(1).CurrentRegion.Rows

  With CreateObject("ADODB.Stream")
  .Charset = "Windows-1252"
  .Open

  For R& = 1 To Rg.Count
  .WriteText """" & Join(Application.Index(Rg.Item(R).Resize(2).Value, 1), """;""") & """", 1
  Next

  .SaveToFile ThisWorkbook.Path & "\Export.csv", 2
  .Close
  End With

  Set Rg = Nothing
  MsgBox "Done in " & Format(Timer - T, "0.000s !"), vbExclamation, "  Export Demo3"
End Sub

Marc L · Dec 23, 2015

I just try Demo3 with your workbook without any issue :

So try on your side Demo2 …

Text file generated by Demo3 :

Jerry Hall · Dec 23, 2015

Hi Marc, I tried #3 again and #2 as well, both with similar result - not getting first column to be quoted. Could this be a setting in my excel to ignore ID columns (I saw a thread on the web about that being an issue in Excel ~2003). Or perhaps another setting? My process is I open the file as an .xlsm, create a module, save the file, run the script/module and then open and check the Export file. Everything works like a charm except the first column. Thanks again!

Procedure to export a text file with both comma and quote delimiters in Excel

Member

Excel Ninja

Excel Ninja

Excel Ninja

Attachments

Member

Attachments

Excel Ninja

Member

Member

Excel Ninja

Member

Excel Ninja

Excel Ninja

Member

Member

Excel Ninja

Excel Ninja

Member

Member

Excel Ninja

New Member

New Member

Excel Ninja

New Member

Attachments

Excel Ninja

Attachments

New Member