Solved

how can I conert a excel file to csv quoted text ?

Posted on 2014-10-30
4
149 Views
Last Modified: 2014-11-05
I need to get to the data in an excel spreadsheet.   I have tried excel to csv however there are line feeds (hex 20 ) within many of the cells.  

I tried some free macros that seemed to quote the data however rather than getting  "quote me" , I would get """quote me""".

After trying to modify the script by reducing the quotes, the script started doing very strange things and did not like my Linux fingers on it  ..... :)

can I convert excel to csv with quotes ?
0
Comment
Question by:TIMFOX123
4 Comments
 
LVL 39

Expert Comment

by:als315
ID: 40415121
Can you upload samples (excel source and expected csv output)?
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40415849
This subroutine will produce a CSV file using data on the active sheet.  Line breaks will be ignored.
Sub CSVFile()
    'This produces a CSV file with quotes around all data
    Dim SrcRg As Range
    Dim CurrRow As Range
    Dim CurrCell As Range
    Dim CurrTextStr As String
    Dim ListSep As String
    Dim FName As Variant
    FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")

    If FName <> False Then
        ListSep = Application.International(xlListSeparator)
        If Selection.Cells.Count > 1 Then
            Set SrcRg = Selection
        Else
            Set SrcRg = ActiveSheet.UsedRange
        End If
        Open FName For Output As #1
        For Each CurrRow In SrcRg.Rows
            CurrTextStr = ""
            For Each CurrCell In CurrRow.Cells
                CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep
            Next
            While Right(CurrTextStr, 1) = ListSep
                CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
            Wend
            Print #1, CurrTextStr
        Next
        Close #1
    End If
End Sub

Open in new window


You'll be prompted for a file name for the CSV file.  Once done, it will write a CSV file leaving the workbook unaffected.  I recommend opening the CSV file with a text editor to check the output.

This isn't original code; there are several instances of it on the Internet.  But it's worked for me and I had added it to my "toolkit".

-Glenn
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40417602
line feeds (hex 20 )
Hex 20 characters are spaces, not line feeds.  Line feeds are Hex 0D (Decimal 13) characters.


Have you tried replacing the line feed characters with "\n" strings?

What happens if you save the Excel worksheet as a tab-delimited file, rather than a CSV format?
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question