Free Microsoft Excel 2013 Quick Reference

Create csv file with double quotes on all fields

When I create csv files, I need to have all columns wrapped in double-quotes. Currently, Excel only wraps those columns that contain commas in double-quotes. I have access to both Excel 2003 and 2007. Is there a way to configure Excel to wrap all columns and if not, is there some VB magic that I can run to do this?

I tried to search the forum to see if this had already been discussed, but did not find anything.

Thanks,

Jack


I want to create a CSV file from excel. But the additional requirement is I want value in each cell to be enclosed with double quotes. For eg
Now I am getting the casv format as
Value1,value2,value3

This should be "Value1","Value2","Value3"

Is there a way in excel to get the csv file created in the above way.

Please help.

Thanks & Regards
Anand

Hi,

I have 4 csv files with 1000000 rows in all of them.

I need to somehow sort all these at one place and by A-Z

Is this possible?

I'm trying to create an csv type file with double quote around selected cell
values

I regularly use Excel to edit CSV files, since it is often easier to edit
these files in Excel's cell-based format as opposed to a plain text editor,
but in many respects Excel is not exactly easy to use with CSV files.

As an example, open a CSV file and make a change. Now try and shut down
Excel, saving your changes. You will get a series of several dialog boxes,
starting with a "Save As..." dialog, then a "Are you sure you want to
overwrite the file?" dialog, then the "Incompatible features" dialog, then
finally the program exits. This long stream of dialog boxes is pretty
annoying. Hitting "Save" before "Exit" allows one to skip the "Save As..."
dialog, but then you get another "Do you want to save your changes?" dialog
as soon as you try to exit.

Another issue revolves around templates. When opening a CSV file, Excel
seems to ignore default templates stored in XLSTART. This means it is
impossible to add your favorite formatting options to the default template,
so that they are available whenever you open a CSV file. In fact, when
opening a CSV file by double-clicking on it, it causes a problem inside of
Excel, where Excel will always ignore default templates, until you shut down
and restart Excel without double-clicking on a document. For example,
double-click on a CSV file. Then go to "File->New" and create a blank
workbook. The blank workbook will not use the default template stored in
XLSTART. You must shut down and restart Excel to get it to start using the
default template again.

Then there is the double-quote issue. This is more an issue caused by the
fact that there is no universal software standards body, so there is no
universal standard format for CSV files. But essentially, any field in a CSV
file that contains a double-quote or a comma has extra double-quotes placed
around it internally by Excel. These extra quotes can be seen by opening the
CSV file in a program like Notepad. Using double-quotes in this way allows
Excel to have commas inside of a field in a CSV file, which is good, but many
other programs that utilize CSV files do not do this. This makes CSV files
edited in Excel incompatible with many other programs, with no way of making
them compatible short of something like writing a custom Lisp routine to
translate between Microsoft Excel CSV and the "other" CSV format, where
double-quotes are not treated as special characters.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi, I have a Windows NT with Excel 97. I have a very simple csv file
containing:

1;2;3

When I open this test.csv file from File->Open in Excel it renders the
data in three different columns. If I open the file by double-clicking
in explorer it launches Excel and the data if confined in the first
column. It seems as if it doesn't do the same when opening in both
ways.

Any hint?

I need to be able to save a spreadsheet as a csv file, using comma delimited
but also having double quotes around each field. I'm running Excel 2000, on
Windows XP sp2. Any help would be appreciated.

I have a macro that exports a file to a CSV file with the fields enclosed in quotes, however there in items in some of the fields that are already quoted is there a way to modify this macro to ignore quoted items:

for ex: <ul class="test></ul>
is becoming
<ul class=""test""></ul>

Public Sub OutputQuotedCSV()
Const QSTR As String = """"
Dim myRecord As Range
Dim myField As Range
Dim vFilename As Variant
Dim nFileNum As Long
Dim sOut As String

'Get a filename to save as
vFilename = Application.GetSaveAsFilename(filefilter:="Microsoft CSV files,*.csv", _
Title:="Save as CSV with fields in double quotes")

If vFilename = False Then Exit Sub 'User chose Cancel

nFileNum = FreeFile
Open vFilename For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & "," & QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub
nevermind it works correctly

I have two CSV files that I would like to edit through Excel VBA.

Both files have too many lines to be shown in Excel though, so I can't just use cell rerences.

Every so many lines I want to delete a row and insert a row from one CSV file to the other.

I could easily write a loop do this with cell references, but since all the lines aren't shown I can't do that.

I have no idea how to reference all the rows in the CSV file with VBA when most of them aren't viewable on the worksheet.

Any references or thoughts would be appreciated!

need to export part of row as csv file with column e plus .bom extension
as file name.

a b c d e f
g h i j k l m n o p q r
s t u

done1 11/28/05 312944 862423 599-020d p a g
32 10/25/06
g:sigmasndata55parts55 done2 action cell

cells would be s,c,e,h,i,f,d,,,j,,,,t

notice cell j in csv file will have to be date as shown 2006/10/06
instead of the 10/25/06 that excel file shows in row,, because this is how
the date is inserted into list it is pasted as text from a as400 dat file ..
g:sigmasndata55parts55312944599-020d,g,32,p,862423,,,2006/10/25,,,,done2file would be saved as c:tmp599-020d.bomexample cells in column u could have the action code in each cell pasteddown sheet ..like row 1 click on cell in u1 and csv file would be created of row 1like row 2 click on cell in u2 and csv file would be created of row 2rod

Hi,

When I was running the macro to create CSV files from excel spreadsheet, I came across new issue. It used to work fine earlier. There are extra spaces in cell. When i just try =trim(a1), it is removing the extra spaces and working fine. But when I put it in macro and run it, it is giving an error.

I have the below text in cell. I put HTML tags around text. Otherwise it is removing extra spaces. I have also attached speadsheet with this text.

HTML Code: 


Sub SaveAllSheetsAsCSV()
    Dim wks As Worksheet
    Dim strFileFullName As String, strFileName As String, strCSVname As String, csvfile As String, lngRows As Long, lngCols
As Long
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    'save the originating file name and path
    strFileFullName = ThisWorkbook.FullName
    'save the originating file name without type
    strFileName = Left(ThisWorkbook.Name, InStr(1, ThisWorkbook.Name, ".") - 1)
    
    'convert all sheets in the originating file as csv files
    'N.B. don't convert "RevisionHistory"
    For Each wks In ThisWorkbook.Worksheets()
        If wks.Name = "DatasetMetadata" Then
          csvfile = "dsmeta"
        ElseIf wks.Name = "ValueLevel" Then
                csvfile = "vlmeta"
        ElseIf wks.Name = "ComputationalAlgorithms" Then
                csvfile = "cameta"
        ElseIf wks.Name = "ExternalControlledTerminology" Then
                csvfile = "ectmeta"
        ElseIf wks.Name = "ControlledTerminology" Then
                csvfile = "ctmeta"
        Else: csvfile = wks.Name
        End If
        
        ' Remove Carriage Retruns
         For lngRows = 1 To 100
            For lngCols = 1 To 20
                If Len(wks.Cells(lngRows, lngCols)) > 0 Then
                      If (InStr(1, wks.Cells(lngRows, lngCols), vbCrLf, vbTextCompare) > 0) Or _
                      (InStr(1, wks.Cells(lngRows, lngCols), vbLf, vbTextCompare) > 0) Then
                          wks.Cells(lngRows, lngCols) = Replace(wks.Cells(lngRows, lngCols), vbCrLf, "")
                       wks.Cells(lngRows, lngCols) = Replace(wks.Cells(lngRows, lngCols), vbLf, "")
                     End If
               End If
          Next lngCols
        Next lngRows
         

        ' Remove Extra spaces
       For lngRows = 1 To 100
           For lngCols = 1 To 20
            If Len(wks.Cells(lngRows, lngCols)) > 0 Then
                wks.Cells(lngRows, lngCols) = WorksheetFunction.Trim(wks.Cells(lngRows, lngCols))
              End If
        Next lngCols
      Next lngRows

        
        If wks.Name <> "RevisionHistory" Then
              strCSVname = ActiveWorkbook.Path & _
                     "" & LCase(csvfile) & ".csv"
            wks.SaveAs Filename:=strCSVname, FileFormat:=xlCSV, CreateBackup:=False
        End If
    Next
    
    
    'Reopen the original Excel file
    Workbooks.Open strFileFullName
    
    Rem: Always set alerts and screen updating back to true
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    'Close the last csv file showing in Excel window
    ThisWorkbook.Close SaveChanges:=False

End Sub
Does any one have any idea why it is not working.

Thanks a lot for your help.

Hi all,

I have to open csv files with possibly more than 65000 rows of data in them,
and all the rows greater than 1 worksheet have to be put on the next
sheet...when that is full I have to add another sheet and so on...

Due to the limitations of Excel, I can't even tell the text Import
(Querytables) method to start at row 65000 as the textFileStartRow parameter
is supposed to be an integer (DOH !) - who came up with that one?

So, what is the best option, open the files using OLEDB Text Provider in ADO
and load from recordset to read them in chunks of 65000 odd records, or is
there a better way?

Or should I use the Textfile Import Method, and read it in in chunks of say
30000 by setting the textFileStartRow property to current Value + 30000 for
each iteration until completed?

thanks for any help or ideas or sympathy...

Philip

I am a little lost in making this work.

I found a few examples around the web of exporting to CSV with Double Quotes. For example:

http://www.markinns.com/articles/ful..._double_quotes

http://windowssecrets.com/forums/sho...-around-values

But I can't come up with a way to export to CSV with the first column not receiving the double quotes.

Any thoughts?

Hi,

I have a very big import to do from Excel 2002 to a .txt file. I need
double quotes around all text fields, but I get everything but. Can
someone pleeeese tell me how to do this.

Do I need the format of the fields to be general or text? Do I start
with single quotes, double quotes, no quotes or maybe even two single
quotes? I've tried everything I can think of.

I even tried going through a .csv file, but then I get three double
quotes!

Thanks a bunch,

Michele

Hi,
I been trying to find a way to remove double quote on null column on my CSV file but can not find any example. Is there anyone out there that can help me? Any help is appreciated.. Thanks.

Here is a sample line from the csv file I get

"2007-02-21 11:07:44.0","MCKINNEY","SC","1172084864684"

When I double click the file it is opened in Excel but it comes in
looking like

07:44.0 MCKINNEY SC 1.17208E+12

I want this data shown in excel exactly as it was read in. As you will
note all the "fields" in the csv line are in "s to denote they are text.
How can this be accomplished?

Thanks

I can open any of my excel file by double clicking on the short cut or file
folder, but if I go into excel and then open from within excel everything is
fine.

I have tried open by right clicking and choosing to open with excel and that
does not work

Any sugestions

Is there an obvious way, when saving as .csv retaining the
double-quotes around *all* of the data elements? Thanks.

Hi all,

I'm trying to open a CSV-File with double-quotation mark and comma.
Afterwards i need to make some checkings in this file and save the correct
data again.

The source data looks like this: "019292","Placards","1"
The result when saving is this: 19292,Placards,1

Leading zero is missing and the double quotation mark is missing.

My code is this:

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(xlsdatei, xlMSDOS, 1, xlDelimited,
xlTextQualifierDoubleQuote, False, False, True, False, False, False)
xlBook.SaveAs FileName:="C:test.csv", FileFormat:=xlCSV
xlBook.Close SaveChanges:=False

Please help. Thank you!

Regards
kaiserlich

Could someone please direct me to a better script or modify the below so i can import a csv file with each line on a seperate row with all values on that line seperated by ',' to be placed in seperate columns?

Here is the format of the CSV file:
"1","Medium","Fixed","","ABC","CCB-A","17/06/2004 9:39:19 AM","Several codeline issues"

This subroutine takes over 40 secs to complete on a P4 machine, when i used 'EDIT TEXT IMPORT' it only took 3 secs.

    MsgBox ("Select a StarTeam CSV file to
import")
    FName = Application.GetOpenFilename _
    (filefilter:="Text Files(*.txt),*.txt")
    If FName = False Then
        MsgBox "You didn't select a file"
        Exit Sub
    End If
ImportTextFile1 CStr(FName), ","

Sub ImportTextFile1(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Application.ScreenUpdating = False
SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
Application.StatusBar = "IMPORTING TEXT FILE........"

Open FName For Input Access Read As #1
While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Replace(Mid(WholeLine, Pos, NextPos - Pos), """", "")
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
Wend
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub


I have a csv file with 100,000 records on it, I know excel only downloads up
to a little over 65,000, and I have tried the Wizard, but it won't let me
export specific rows after about 32,000. I have also tried to change the csv
file to a Word file and cut it into two seperate files, but then Excel won't
recognise the format. There must be a way!
PS I don't have Access

Hi there,

I am trying to write a macro to create CSV files from excel that are used to import into a workforce management application.
the CSV files are of different lenghts but always 8 columns long

I have got as far as sucsessfully creating the import document in the correct format but it is creating an extra line at the bottom of each CSV file that means at the moment i have to open each of them with notepad and delete the extra line before they are formatted correctly.

I believe there is a simple way of removing this last carraige return as i save the document but at present its driving me mad

any ideas?

cheers,

B_F

Hi

I'm using an IF function that replaces [square brackets] with "double quotes" (it has to be double quotes)

How do I specify that the double quote character is inserted as a piece of text without Excel seeing the quotes as a command to end the string of text to be replaced?

Hello,

In a Excel Macro (VBA) I'm trying to save a ".xls" file into a ".csv
file with SEMICOLON ";" delimiters. There is the code :

ActiveWorkbook.SaveAs FileName:="test", _
FileFormat:=xlCSV, CreateBackup:=False

But when I open the result file, the delimiters are comma ",". Do yo
have a solution to save the file with semicolon ";" ?

Thank you very much in advance

--
Message posted from http://www.ExcelForum.com

Hello,

How do I create VBA macro to create CSV files out of Excel spread sheet for each tab? Right now I am manually opening the spreadsheet and saving each tab as CSV file. But it is taking lot of time if we have lot of tabs.

Any help would be appreaciated !!

Thanks,
/Sukanya