• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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
 
Last edited by a moderator:

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 …
 
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
 

Attachments

  • Export CSV File.xlsm
    17.4 KB · Views: 7
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
 

Attachments

  • Sample Data.xlsm
    21.9 KB · Views: 6
  • CONSUMPTION_BRIO_LOAD.zip
    1.8 KB · Views: 2

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 !
 
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..
 
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
 

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

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 ! :DD
 

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
 
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..
 
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
 

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 ?
 

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 !
 
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"
 
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 …
 
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
 

Attachments

  • Pre-Export_sample.xlsm
    11.6 KB · Views: 10
Last edited:
I just try Demo3 with your workbook without any issue :​

ExportCsv.jpg

So try on your side Demo2 …

Text file generated by Demo3 :​
 

Attachments

  • Export.csv.txt
    7.3 KB · Views: 10
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!
 
Last edited:
Back
Top