Solved

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

Posted on 2014-10-30
4
145 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now