[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

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 ?
0
TIMFOX123
Asked:
TIMFOX123
1 Solution
 
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now