Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel CSV export (2000 SR1)

    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

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel CSV export (2000 SR1)

    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/. 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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel CSV export (2000 SR1)

    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.

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

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

    Regards

    Veronica

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

    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

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

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

  8. #8
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

    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!

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

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

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

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

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

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

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

  12. #12
    Star Lounger
    Join Date
    Jan 2001
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

    > 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

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

    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?
    Legare Coleman

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

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

    <pre>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
    </pre>

    Legare Coleman

  15. #15
    Star Lounger
    Join Date
    Jan 2001
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel CSV export (2000 SR1)

    Legare, you must be pretty sharp. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    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!

Posting Permissions

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