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

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 ?
TIMFOX123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
Can you upload samples (excel source and expected csv output)?
0
Glenn RayExcel VBA DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.