Excel CSV export (2000 SR1)

Topic

New Reply

This seems like a really basic question, but I’m not a frequent user of Excel and there doesn’t seem an obvious way of doing it. I want to export a spreadsheet as a CSV file, with semi-colons for delimiters, and quotes round text values (i.e. columns formatted as text). I eventually found out that I needed to edit regional settings in the control panel to get the semi-colon (obvious, huh??) — but how do I force quotes round text values?

Thanks for your help!

Veronica

Reply | Quote

Replies

I don’t think that you have the option of forcing Excel to put quotes around each text field. However, Earl Kiosterud has written a “write text program” that is available on http://www.tushar-mehta.com/%5B/url%5D. Just click on the Text Write Excel AddIn link on the nav bar. It allows you to specify the field deliminiter (; for you) and a bracketing character (” for you). It says that it is 10 times faster that Excel’s CSV write, so I think that I will download it and try it out. HTH –Sam

Reply | Quote

Hi Sam

Thanks for that quick and helpful response! It looks like a useful program. But actually, in the meantime, I rewrote the program that does the database import to put quotes round columns that need them (it does this by checking the data type of the equivalent field in the database). Still, the “Write text” program could save me some work in future!

I had a vague memory of some version of Excel in the past that had a button in the “Save as” box that let you specify options, or even a template, when saving as text. But perhaps I’m thinking of Access.

Thanks anyway!

Regards

Veronica

Reply | Quote

You were probably thinking of Access. When you export an Access table or query to a text file, the Export Text Wizard kicks in, giving you complete control over how the data are exported. You can store your settings as an export specification for later re-use.

Reply | Quote

Thanks! In that case I guess I could have got round the problem by importing into Access, then re-exporting as required …

Regards

Veronica

Reply | Quote

That “text write” thing sure is nifty – but it doesn’t do quite what he asked. What he asked for (and I need too!) is the ability to export with quotes around text, and no quotes around numbers:
7,3.14159,”Dave”,55378

I hope somebody’ll come up with SOMEthing – it’s kind of amazing that such an obvious thing doesn’t exist! (But what do I know…)

The MS support site has VBA code to export with quotes, but it too is unable to treat numbers differently.

Dave

Reply | Quote

That “text write” thing sure is nifty – but it doesn’t do quite what he asked. What he asked for (and I need too!) is the ability to export with quotes around text, and no quotes around numbers:
7,3.14159,”Dave”,55378

I hope somebody’ll come up with SOMEthing – it’s kind of amazing that such an obvious thing doesn’t exist! (But what do I know…)

The MS support site has VBA code to export with quotes, but it too is unable to treat numbers differently.

Dave

Reply | Quote

There is no way to control how Excel outputs CSV files. Not only that, there are a number of bugs in the code that cause very wierd things to happen when saving in CSV format. For example, the format of data from a column will change part way through the file for no apparent reason. Excel may, all of a sudden, in the middle of the file stop inserting commas for empty columns. I would not trust using Excel to output CSV files.

If you need to reliably output data in CSV format, you might want to consider writing VBA code to do the job.

Reply | Quote

Legare,
I’ve been having CSV/Excel problems (post 355681) that almost seem to be machine dependent. Your comment “bugs in the code that cause very wierd things to happen when saving in CSV format” has helped restore my sanity as it’s probably not my fault!

Reply | Quote

Legare,
I’ve been having CSV/Excel problems (post 355681) that almost seem to be machine dependent. Your comment “bugs in the code that cause very wierd things to happen when saving in CSV format” has helped restore my sanity as it’s probably not my fault!

Reply | Quote

I think that your thread is on XL 2002. Since I will not install a product that includes the registration/activation wizard, I can’t comment on that version. My comments are for XL2K and below. However, I would not be surprised if they were also appropriate for newer releases.

Reply | Quote

I think that your thread is on XL 2002. Since I will not install a product that includes the registration/activation wizard, I can’t comment on that version. My comments are for XL2K and below. However, I would not be surprised if they were also appropriate for newer releases.

Reply | Quote

> bugs

Huh! Thanks.

Gee, considering that it CAN’T be very complicated to output text with commas and quotes, you MIGHT think that Microsoft might intentionally WANT to discourage people from taking data OUT of Excel!

Sounds like a job for a trivial string-search-and-replace utility, since I don’t do VBA… thanks for the reply.

Reply | Quote

If you know what your spreadsheet looks like, and what you want in the output, the VBA to do this should be fairly simple. We’d could help if you upload a sample worksheet and describe what you want in the output file.

Reply | Quote

> We could help if you upload a sample worksheet and describe what you want in the output file.

Wellll, let’s see – I’ve never uploaded an attachment here, but I’ll give it a whack. There are just two lines of sample data.

The output should simply be exactly like the original (unless, of course, the content has been edited!) Comma-delimited, with quotes around everything except all-number fields.

This particular application doesn’t have commas inside fields, so for my part, it wouldn’t matter how you handle them.

Hmmm… just noticed: I haven’t found out yet whether it’s important to pad the fields that seem to be fixed-length…. I think not, but if it is, I’ll let you know.

Thanks for exploring this, including your offline remarks – much appreciated.

Dave

Reply | Quote

That’s close to what I need to help with this. However, from a text file I can’t tell what will be in the spreadsheet for some fields. For example, is “10/23/03” actually stored in the spreadsheet as a string, or is it a Excel date value. Most of them are fairly obvious, but there are a few that I can’t be sure of. Could you reply to this message and attach an Excel workbook with those two records stored the way they will be in the actual workbook?

Reply | Quote

That’s close to what I need to help with this. However, from a text file I can’t tell what will be in the spreadsheet for some fields. For example, is “10/23/03” actually stored in the spreadsheet as a string, or is it a Excel date value. Most of them are fairly obvious, but there are a few that I can’t be sure of. Could you reply to this message and attach an Excel workbook with those two records stored the way they will be in the actual workbook?

Reply | Quote

Ok, I made a guess at what things were. Try the VBA routine below as see if it does what you want.

Public Sub WriteCSV()
Dim vFile As Variant, vIFile As Variant
Dim iFile As Integer, lLastRow As Long, I As Long
Dim strWk As String
    vIFile = ActiveWorkbook.Name
    If InStr(vIFile, ".") > 0 Then
        vIFile = Left(vIFile, InStrRev(vIFile, ".") - 1)
    End If
    vIFile = vIFile & ".csv"
    vFile = Application.GetSaveAsFilename(vIFile, "CSV Files (*.csv), *.csv,All Files (*.*), *.*", 1, _
      "Enter CSV Filename")
    If vFile = False Then Exit Sub
    iFile = FreeFile()
    Open vFile For Output As #iFile
    With ActiveSheet
        lLastRow = .Range("A65536").End(xlUp).Row - 1
        For I = 0 To lLastRow
            strWk = ""
            With .Range("A1")
                strWk = strWk & .Offset(I, 0).Value
                strWk = strWk & ",""" & Format(.Offset(I, 1).Value, "mm/dd/yy") & """"
                strWk = strWk & ",""" & Format(.Offset(I, 2).Value, "mm/dd/yy") & """"
                strWk = strWk & ",""" & .Offset(I, 3).Value & """"
                strWk = strWk & ",""" & .Offset(I, 4).Value & """"
                strWk = strWk & ",""" & .Offset(I, 5).Value & """"
                strWk = strWk & ",""" & .Offset(I, 6).Value & """"
                strWk = strWk & ",""" & .Offset(I, 7).Value & """"
                strWk = strWk & "," & .Offset(I, 8).Value
                strWk = strWk & "," & .Offset(I, 9).Value
                strWk = strWk & ",""" & .Offset(I, 10).Value & """"
                strWk = strWk & "," & Format(.Offset(I, 11).Value, "000")
                strWk = strWk & "," & .Offset(I, 12).Value
                strWk = strWk & ",""" & .Offset(I, 13).Value & """"
                strWk = strWk & "," & Format(.Offset(I, 14).Value, "00.00")
                strWk = strWk & "," & Format(.Offset(I, 15).Value, "00.00")
                strWk = strWk & "," & Format(.Offset(I, 16).Value, "00.00")
                strWk = strWk & "," & Format(.Offset(I, 17).Value, "00.00")
                strWk = strWk & ",""" & .Offset(I, 18).Value & """"
                strWk = strWk & ",""" & .Offset(I, 19).Value & """"
                strWk = strWk & ",""" & .Offset(I, 20).Value & """"
                strWk = strWk & ",""" & .Offset(I, 21).Value & """"
                strWk = strWk & ",""" & .Offset(I, 22).Value & """"
                strWk = strWk & ",""" & .Offset(I, 23).Value & """"
                strWk = strWk & ",""" & .Offset(I, 24).Value & """"
                strWk = strWk & ",""" & .Offset(I, 25).Value & """"
                strWk = strWk & ",""" & .Offset(I, 26).Value & """"
                strWk = strWk & ",""" & .Offset(I, 27).Value & """"
                strWk = strWk & ",""" & .Offset(I, 28).Value & """"
                strWk = strWk & ",""" & .Offset(I, 29).Value & """"
                strWk = strWk & "," & .Offset(I, 30).Value
                strWk = strWk & "," & .Offset(I, 31).Value
                strWk = strWk & "," & .Offset(I, 32).Value
                strWk = strWk & ",""" & .Offset(I, 33).Value & """"
                strWk = strWk & "," & .Offset(I, 34).Value
                strWk = strWk & "," & .Offset(I, 35).Value
                strWk = strWk & ",""" & .Offset(I, 36).Value & """"
                strWk = strWk & "," & .Offset(I, 37).Value
                strWk = strWk & "," & .Offset(I, 38).Value
                strWk = strWk & ",""" & .Offset(I, 39).Value & """"
                strWk = strWk & "," & Format(.Offset(I, 40).Value, "000000")
                strWk = strWk & ",""" & .Offset(I, 41).Value & """"
                strWk = strWk & ",""" & .Offset(I, 42).Value & """"
                strWk = strWk & ",""" & .Offset(I, 43).Value & """"
                Print #iFile, strWk
            End With
        Next I
    End With
    Close #1
End Sub

Reply | Quote

Legare, you must be pretty sharp.

I’m new to macro-using (in Excel), but I guess I got it figured out, because I run the thing and it asks me for a filename and the result matches the original. Heh.

Now I’ll go ask my users if it’s what they wanted, or if there’s anything they forgot to tell me…

I’ll be back – thanks!

Reply | Quote

Legare, you must be pretty sharp.

I’m new to macro-using (in Excel), but I guess I got it figured out, because I run the thing and it asks me for a filename and the result matches the original. Heh.

Now I’ll go ask my users if it’s what they wanted, or if there’s anything they forgot to tell me…

I’ll be back – thanks!

Reply | Quote

Ok, I made a guess at what things were. Try the VBA routine below as see if it does what you want.

Public Sub WriteCSV()
Dim vFile As Variant, vIFile As Variant
Dim iFile As Integer, lLastRow As Long, I As Long
Dim strWk As String
    vIFile = ActiveWorkbook.Name
    If InStr(vIFile, ".") > 0 Then
        vIFile = Left(vIFile, InStrRev(vIFile, ".") - 1)
    End If
    vIFile = vIFile & ".csv"
    vFile = Application.GetSaveAsFilename(vIFile, "CSV Files (*.csv), *.csv,All Files (*.*), *.*", 1, _
      "Enter CSV Filename")
    If vFile = False Then Exit Sub
    iFile = FreeFile()
    Open vFile For Output As #iFile
    With ActiveSheet
        lLastRow = .Range("A65536").End(xlUp).Row - 1
        For I = 0 To lLastRow
            strWk = ""
            With .Range("A1")
                strWk = strWk & .Offset(I, 0).Value
                strWk = strWk & ",""" & Format(.Offset(I, 1).Value, "mm/dd/yy") & """"
                strWk = strWk & ",""" & Format(.Offset(I, 2).Value, "mm/dd/yy") & """"
                strWk = strWk & ",""" & .Offset(I, 3).Value & """"
                strWk = strWk & ",""" & .Offset(I, 4).Value & """"
                strWk = strWk & ",""" & .Offset(I, 5).Value & """"
                strWk = strWk & ",""" & .Offset(I, 6).Value & """"
                strWk = strWk & ",""" & .Offset(I, 7).Value & """"
                strWk = strWk & "," & .Offset(I, 8).Value
                strWk = strWk & "," & .Offset(I, 9).Value
                strWk = strWk & ",""" & .Offset(I, 10).Value & """"
                strWk = strWk & "," & Format(.Offset(I, 11).Value, "000")
                strWk = strWk & "," & .Offset(I, 12).Value
                strWk = strWk & ",""" & .Offset(I, 13).Value & """"
                strWk = strWk & "," & Format(.Offset(I, 14).Value, "00.00")
                strWk = strWk & "," & Format(.Offset(I, 15).Value, "00.00")
                strWk = strWk & "," & Format(.Offset(I, 16).Value, "00.00")
                strWk = strWk & "," & Format(.Offset(I, 17).Value, "00.00")
                strWk = strWk & ",""" & .Offset(I, 18).Value & """"
                strWk = strWk & ",""" & .Offset(I, 19).Value & """"
                strWk = strWk & ",""" & .Offset(I, 20).Value & """"
                strWk = strWk & ",""" & .Offset(I, 21).Value & """"
                strWk = strWk & ",""" & .Offset(I, 22).Value & """"
                strWk = strWk & ",""" & .Offset(I, 23).Value & """"
                strWk = strWk & ",""" & .Offset(I, 24).Value & """"
                strWk = strWk & ",""" & .Offset(I, 25).Value & """"
                strWk = strWk & ",""" & .Offset(I, 26).Value & """"
                strWk = strWk & ",""" & .Offset(I, 27).Value & """"
                strWk = strWk & ",""" & .Offset(I, 28).Value & """"
                strWk = strWk & ",""" & .Offset(I, 29).Value & """"
                strWk = strWk & "," & .Offset(I, 30).Value
                strWk = strWk & "," & .Offset(I, 31).Value
                strWk = strWk & "," & .Offset(I, 32).Value
                strWk = strWk & ",""" & .Offset(I, 33).Value & """"
                strWk = strWk & "," & .Offset(I, 34).Value
                strWk = strWk & "," & .Offset(I, 35).Value
                strWk = strWk & ",""" & .Offset(I, 36).Value & """"
                strWk = strWk & "," & .Offset(I, 37).Value
                strWk = strWk & "," & .Offset(I, 38).Value
                strWk = strWk & ",""" & .Offset(I, 39).Value & """"
                strWk = strWk & "," & Format(.Offset(I, 40).Value, "000000")
                strWk = strWk & ",""" & .Offset(I, 41).Value & """"
                strWk = strWk & ",""" & .Offset(I, 42).Value & """"
                strWk = strWk & ",""" & .Offset(I, 43).Value & """"
                Print #iFile, strWk
            End With
        Next I
    End With
    Close #1
End Sub

Reply | Quote

> We could help if you upload a sample worksheet and describe what you want in the output file.

Wellll, let’s see – I’ve never uploaded an attachment here, but I’ll give it a whack. There are just two lines of sample data.

The output should simply be exactly like the original (unless, of course, the content has been edited!) Comma-delimited, with quotes around everything except all-number fields.

This particular application doesn’t have commas inside fields, so for my part, it wouldn’t matter how you handle them.

Hmmm… just noticed: I haven’t found out yet whether it’s important to pad the fields that seem to be fixed-length…. I think not, but if it is, I’ll let you know.

Thanks for exploring this, including your offline remarks – much appreciated.

Dave

Reply | Quote

If you know what your spreadsheet looks like, and what you want in the output, the VBA to do this should be fairly simple. We’d could help if you upload a sample worksheet and describe what you want in the output file.

Reply | Quote

> bugs

Huh! Thanks.

Gee, considering that it CAN’T be very complicated to output text with commas and quotes, you MIGHT think that Microsoft might intentionally WANT to discourage people from taking data OUT of Excel!

Sounds like a job for a trivial string-search-and-replace utility, since I don’t do VBA… thanks for the reply.

Reply | Quote

There is no way to control how Excel outputs CSV files. Not only that, there are a number of bugs in the code that cause very wierd things to happen when saving in CSV format. For example, the format of data from a column will change part way through the file for no apparent reason. Excel may, all of a sudden, in the middle of the file stop inserting commas for empty columns. I would not trust using Excel to output CSV files.

If you need to reliably output data in CSV format, you might want to consider writing VBA code to do the job.

Reply | Quote