Solved

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

Posted on 2014-10-30
4
152 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 40

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

749 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