Missing commas in csv save

Hi

I have a question about saving to csv files, in some circumstances a
leading "," is being dropped and I wanted to know if there is a way
around it.

Explanation of situation first, then the specific question releated to
the situation at the end.


I have some code which copies a table from an Excel workbook, pastes
it into a new workbook, and then saves that workbook as a csv file:

Code something like:
Application.Goto Reference:=strDataRange
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
    strOutpath & "\" & strDataRange & ".csv", _
    FileFormat:=xlCSV, _
    CreateBackup:=False
    ActiveWindow.Close SaveChanges:=False


Of particular interest is a table which has a heading row where the
first column has no header at all.
eg <blank>  myFirstHeaderCol  mySecondHeaderCol etc
     data1      data2                    data3
     data4      data5                    data6
etc

Normally this gets saved into a csv file as:
,myFirstHeaderCol,mySecondHeaderCol,<etc>
data1,data2,data3
data4,data5,data6
etc

The important point is that leading "," on the first line

This output is exactly what I want.



Things get interesting if this table has no data.  In these cases the
range is still specified over the table, it is just the table is empty
bar the header row.

When I do the csv save in this case I get the following:
myFirstHeaderCol, mySecondHeaderCol,<etc>

So excel has dropped the first ","

I suppose that without data in the rest of the table Excel didnt know
that I wanted that first column, ie it thought it was doing me a
favour by dropping it like it also drops empty rows etc.


QUESTION:
Is there any way I can get Excel to include every comma even if
subsequent rows etc for that column are empty?

Thanks
Andy C
0
8/14/2008 11:15:05 PM
excel 39879 articles. 2 followers. Follow

2 Replies
602 Views

Similar Articles

[PageSpeed] 15

Put something in that cell.

I like to use:
=""

The cell looks empty, but if I select it, I can see the formula and remember why
I did it.


AC wrote:
> 
> Hi
> 
> I have a question about saving to csv files, in some circumstances a
> leading "," is being dropped and I wanted to know if there is a way
> around it.
> 
> Explanation of situation first, then the specific question releated to
> the situation at the end.
> 
> I have some code which copies a table from an Excel workbook, pastes
> it into a new workbook, and then saves that workbook as a csv file:
> 
> Code something like:
> Application.Goto Reference:=strDataRange
>     Selection.Copy
>     Workbooks.Add
>     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _
>         False, Transpose:=False
>     Application.CutCopyMode = False
>     Application.DisplayAlerts = False
>     ActiveWorkbook.SaveAs Filename:= _
>     strOutpath & "\" & strDataRange & ".csv", _
>     FileFormat:=xlCSV, _
>     CreateBackup:=False
>     ActiveWindow.Close SaveChanges:=False
> 
> Of particular interest is a table which has a heading row where the
> first column has no header at all.
> eg <blank>  myFirstHeaderCol  mySecondHeaderCol etc
>      data1      data2                    data3
>      data4      data5                    data6
> etc
> 
> Normally this gets saved into a csv file as:
> ,myFirstHeaderCol,mySecondHeaderCol,<etc>
> data1,data2,data3
> data4,data5,data6
> etc
> 
> The important point is that leading "," on the first line
> 
> This output is exactly what I want.
> 
> Things get interesting if this table has no data.  In these cases the
> range is still specified over the table, it is just the table is empty
> bar the header row.
> 
> When I do the csv save in this case I get the following:
> myFirstHeaderCol, mySecondHeaderCol,<etc>
> 
> So excel has dropped the first ","
> 
> I suppose that without data in the rest of the table Excel didnt know
> that I wanted that first column, ie it thought it was doing me a
> favour by dropping it like it also drops empty rows etc.
> 
> QUESTION:
> Is there any way I can get Excel to include every comma even if
> subsequent rows etc for that column are empty?
> 
> Thanks
> Andy C

-- 

Dave Peterson
0
petersod (12005)
8/15/2008 12:01:34 AM
>>Excel .. thought it was doing me a favour by dropping it like it also 
>>drops empty rows etc.

Sort of but I'd put it this way - When you save a CSV Excel writes the 'used 
range' to the file.  People commonly think that the used range always starts 
at A1 but it doesn't necessarily.  In a new sheet if you enter data from B1 
down and right and then do this in the Immediate window:

?sheet1.UsedRange.Address

you get something like this:

$B$1:$F$14

So doing what Dave suggested will include A1 in the used range.

-- 
Jim
"AC" <andrewfreestuff@gmail.com> wrote in message 
news:a84ff932-f60f-499b-b5b6-6a6b90bde824@p31g2000prf.googlegroups.com...
| Hi
|
| I have a question about saving to csv files, in some circumstances a
| leading "," is being dropped and I wanted to know if there is a way
| around it.
|
| Explanation of situation first, then the specific question releated to
| the situation at the end.
|
|
| I have some code which copies a table from an Excel workbook, pastes
| it into a new workbook, and then saves that workbook as a csv file:
|
| Code something like:
| Application.Goto Reference:=strDataRange
|    Selection.Copy
|    Workbooks.Add
|    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
| SkipBlanks:= _
|        False, Transpose:=False
|    Application.CutCopyMode = False
|    Application.DisplayAlerts = False
|    ActiveWorkbook.SaveAs Filename:= _
|    strOutpath & "\" & strDataRange & ".csv", _
|    FileFormat:=xlCSV, _
|    CreateBackup:=False
|    ActiveWindow.Close SaveChanges:=False
|
|
| Of particular interest is a table which has a heading row where the
| first column has no header at all.
| eg <blank>  myFirstHeaderCol  mySecondHeaderCol etc
|     data1      data2                    data3
|     data4      data5                    data6
| etc
|
| Normally this gets saved into a csv file as:
| ,myFirstHeaderCol,mySecondHeaderCol,<etc>
| data1,data2,data3
| data4,data5,data6
| etc
|
| The important point is that leading "," on the first line
|
| This output is exactly what I want.
|
|
|
| Things get interesting if this table has no data.  In these cases the
| range is still specified over the table, it is just the table is empty
| bar the header row.
|
| When I do the csv save in this case I get the following:
| myFirstHeaderCol, mySecondHeaderCol,<etc>
|
| So excel has dropped the first ","
|
| I suppose that without data in the rest of the table Excel didnt know
| that I wanted that first column, ie it thought it was doing me a
| favour by dropping it like it also drops empty rows etc.
|
|
| QUESTION:
| Is there any way I can get Excel to include every comma even if
| subsequent rows etc for that column are empty?
|
| Thanks
| Andy C 


0
jrrech (1932)
8/15/2008 10:55:54 AM
Reply:

Similar Artilces:

Size doubles with each save
I have 2 files that include over a 1,000 links and formulas. Every time we save the file, the size doubles. It doesn't matter if any changes are made or not. The only way I've found that will bring it back to a normal size, is to Select All Pages, Move to a new book, and save as the original name. Is there a way to find the problem within the file? What might be causing this? Hi you may also check: http://www.contextures.com/xlfaqApp.html#Unused -- Regards Frank Kabel Frankfurt, Germany "Hargrove" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news...

Saving Graphics in Outlook 2000
I belong to a graphics group and from time to time would like to save graphics that come in email. When I right click>save picture as> I get a ..bmp option to save and would prefer to save as either a jpg or a gif. If I manually type in .gif, I loose the animation. Is it possible to save a graphic in the format that it was inserted in the email? Thanks for the help. I have exactly the same problem with Outlook. AND deleting the "ofline content" does not work at all. Can anyone help on this one. ------------------------------------------------ ~~ Message posted from htt...

Excel 2003 adds extra quotes to CSV upon resaving
I have a CSV that when viewed in notepad has the following 2 lines "R1","J","3","","N","4",":4","0" "R1","L","2","","N","4",":4","0" but no matter if i open in by double-clicking or importing the data into a blank worksheet, then resaving, when i open the CSV in notepad, it comes out looking like this, with extra quotes, rather than just the quoting following along with the original 2 lines. """R1"","...

Not saving records till "save" command button clicked
If you are in a form and adding information, then close out or go to a new for it just saves the record as long as all applicable fields are filled out. Is there a way to only have a record save if a command button is clicked? -- Message posted via http://www.accessmonster.com If you simply hide the form instead of closing it, I think that will prevent the save. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "szag via AccessMonster.com" <u2885@uwe> wrote in message news:a75249f4b37bb@uwe... > If you are in a form and adding informat...

Saving
Operating System: Mac OS X 10.5 (Leopard) Processor: intel In windows powerpoint how do I save an "individual" slide (not the whole powerpoint presentation) to a PDF file? Thanks, John Which version? On 9/05/08 4:41 AM, in article ee9ae38.-1@webcrossing.caR9absDaxw, "Full_Bloom@officeformac.com" <Full_Bloom@officeformac.com> wrote: > Operating System: Mac OS X 10.5 (Leopard) > Processor: intel > > In windows powerpoint how do I save an "individual" slide (not the whole > powerpoint presentation) to a PDF file? > Thanks, > John -- ...

Saving e-mail including attachments problem
Saving Outlook e-mail including attachments has alwawys worked until recently. Can still save an e-mail but the attachemnts disappear. Please help! ...

Publisher 2003 printing missing
At my school my students are creating newsletters. On random lines...on each student's printout...there are partial lines printed. Some titles don't print. Students have tried changing the size of the font, the font itself, the size of the text boxes, etc. We are always missing partial lines. It is a different place on each paper and it happens on different printers. Any suggestions? Barbmc wrote: > At my school my students are creating newsletters. On random lines...on each > student's printout...there are partial lines printed. Some titles don't > p...

Which files to save to save the whole OS design?
Hi, I am not sure which files I need to save to be able to rebuild the whole OS design later or on another PC. I read that I don't need to save everything in my OS design folder. But is the solution file and the *.pbxml enough? Thank you for any help Martin Yes.Only .sln and .pbxml files are enough to rebuild your OSDesign folder in another PC. If you have any subprojects created on it then you need to backup the whole subproject folder. -- Regards, Prabu http://prabukumar.wordpress.com/ "Martin M" <MartinM@discussions.microsoft.com> wrote ...

Messages not saved in Sent Items Folder
Outlook 2003. Some messages are not being saved to the Sent Items Folder even though it is checked in the options. Does not matter whether is a reply, forward or a new email. It is seemingly random with most messages being saved correctly to the Sent Items Folder. Any Ideas? Thanks, Hugo I have a similar problem, along the lines of the below: When i type email and attempt to send when not connected (due to temporary connectivity issues and when unable to connect), the mail is saved in Outbox. When next connected, the mail is sent *but* the mail is not saved in sent items folder. Sending wh...

Help...Need to modify data within a column in a .csv file
I have been given a .csv file of several thousand rows, and I need to change column A of each. The brief cutout below shows column A of the first two rows. (I need to skip the first row, which is the headers.) Kinda clumsy to have such a wide column, but the application this file is to be fed after I change the contents requires all the data separated by double-quotes to be in the one column. Cell A2: 09/12/0509/25/05"1417385"121"307"111-11-1111"09/30/05"LASTNAME1 Cell A3: 09/12/0509/25/05"1417386"101"900000005"222-22-2222"09/30/05"...

cell to have comma-delimited values based on text
I have a table called "220_reference" with a column name "Part Number" having a sample value of below: 4047122(All Dash no.), 4057222(All Dash no.), 4058222(All Dash no.), 4060122(All Dash no.) The entire value is in one cell representing the "Part Number" column(defined as general type so text I suppose). Simple enough. But what I need to do is take any number that has "(All Dash no.)" after it and search through a column in another table to retrieve any rows that have that number(text) in it. The other table name is "220" with ...

2003: Copy R-Click Option Missing!
In outlook 2003 when i right-click on an email message, only the move command/option is there (amongst others), but the copy command is missing? the only way I can copy is to drag the message to a folder with the right mouse button depressed and then the copy option shows up. Why is the copy option missing when I right click on an email message? thank you ...

Create CSV
Thanks Jacob Skaria the paste values is working now I was hoping this would fix another issue I am having but it didn't. Once I copy to sheet2 I want to save that sheet as a csv. The code creates an archive copy (with timestamp suffix) and also a working copy (without time stamp) that is uploaded via FTP. This is the code that I am using: Dim strWksheet As String Dim strPath As String Dim strFileName As String Dim strTimeStamp As String strWksheet = "sheet2" strPath = "mypath" strFileName = "NewFile" strTimeStamp = Format(N...

MS Outlook will not start
Unable to display the folder. Microsoft Outlook could not access the specified folder location. The .DLL file for the information service could not be found. MAPI was unable to load the information service PSTPRX.DLL. Be sure the service is correctly installed and configured. Recreate your mailprofile in Control Panel-> Mail-> button Show Profiles... -- Roady [MVP] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Damian ten Bohmer" <damiantenbohmer@iprimus.com.au> wrote in message news:...

problem with dates in csv file
Hi, I'm trying to save a .csv file at the moment, the person who I' sending it to needs the dates in 1 column as dd-mmm-yy, eg 18-Sep-05 eg 09-Sep-05. When I save the file it always reverts the dates 09 o below back to a single number, 9 etc. I've tried copying the column into word, paste special as text formatting the column in excel to text and pasting special back in a text, it works ok but when I save and open the file it reverts back! does anyone know how I could get around this? Many thanks, saybut -- saybu --------------------------------------------------------------...

Saving a filter?
I am sorting a large spreadsheet. I have filtered down to my chosen rows, now I want to save this as a separate sheet! Press CTRL + A Right-click > Copy Click on a new sheet Right-click on A1 > Paste -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "rik84" <rik84@discussions.microsoft.com> wrote in message news:B03A1D87-0215-4E3F-B5C7-23B6D70CFE92@microsoft.com... > I am sorting a large spreadsheet. I have filtered down to my chosen rows, now > I want to save this as a separate sheet! Or use (in a stand...

Office Document Connection
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel No matter how many times I click "save my password in keychain" it always asks me for my password when I open up the document connection. Additionally, when I click "new file" , then edit the file and save it does not save to the location I was on the SharePoint site but locally. Other than that great work, this saves me so much time, I can't wait to have it fully integrated in Office! There might be a problem with the Keychain database. Open Keychain Access, then click on Keychain>Keych...

Saving and Restoring a Child Window to its Former Position and Size
I want to save a child window's position and size in the Main Window of an MDI application, and restore it at any time later. I want to do this after the user may have reposition and resized it. The parent of this child window is the Main Window. Below is the code I am using to save the position and size: pView->GetWindowRect( &m_WndRect ); AfxGetMainWnd()->ScreenToClient( &m_WndRect ); where pView is a pointer to the child window and m_WndRect is used for the storage. Below is the code I am using in an attempt to restore the position and size stored in m_WndRect: CFr...

Office XP icons missing on Windows 7 Pro x64
I installed Office XP on a PC running Windows 7 Pro x64. The icons for the various office programs exist, but are blank. When I look at the shortcuts, it doesn't allow me to choose the location of the icon - it's greyed out. I've run windws explorer elevated, but I still can't change the icon location. If I navigate to the Winword.exe location, it has an icon. If I create a new shortcut, that new shortcut also doesn't have an icon. If I try pinning one to the start menu or taskbar, I get the same problem. I also have Outlook 2007 installed, but the icon for thi...

I need to save templates in a different way. Anyone know a soluti
I am a lawyer building precedents for my law firm. Here is the problem. I have about 100 separate macro-enabled templates created in my documents folder. I have created a separate folder for shortcuts. There I put shortcuts to each of the commonly used templates I have, and I have assigned hotkeys to them (F1-F12, ctrl-alt A-Z). Now here is the problem. Because I cannot save over a template (when I edit it for changes in the law) and instead have to save it with a different name, I lose the connection to the hotkey, which is linked to that particular document. I do not wa...

Windows CE R3 installation
Hi I post here yet another question about the WindowsCE 6.0 R3 instalation. I encountered the fact, that a library is updated during the R3 installation, but the corresponding source file not. I then had a look at the installer file and saw that there the source code file is missing. Well the library i am talking about is the bthci.lib and the (only one) source code file is \PRIVATE\WINCEOS\COMM\BLUETOOTH\HCI\BASE\hci.cxx Does anybody know about this inconsistency? ...

Saving workbook #2
We are sharing some excel books on netwrok by using several "my briefcase" linking to one master file. We cannot modify the file and update to the master file at the same time - as the computer refuse to merge them together. Is there any method that it can go real multiuser. Hi you may try the following: sub save_with_cell() Dim fname fname = worksheets("sheet1").range("A1").value fname = fname & ".xls" ActiveWorkbook.SaveAs Filename:=fname end sub You should add some error checking to this (does the files exist, etc.) -- Regards Fr...

Missing engineering functions
In an earlier version of Excel, I had "engineering functions" like =dec2hex and =hex2dec. Not, in Excel 2003, I see them in the "help files' but not in my list of available formulas. How do I get them to work in Excel 2003? Thanks in advance, Geoff Waters Glendale, CA Geoff, I don't have Excel 2003, but you should be able to go to Tools | Add-ins and check mark "Analysis Took Pak" in the list that appears. Regards, Jim Cone San Francisco, CA "Geoff" <grw888@hotmail.com> wrote in message news:zwfIc.299$Qu5.238@newsread2.news.pas.earthli...

save to sub folder
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel As previous PC user my documents are organized in several layers of folder (folder - sub folder - sub sub folder, etc) When I tried to save email atachement I can only get into the main folder (can not get into sub folder or sub sub folder). Same problem occur when I create a new file (excel, word or PW) when I do 'save as' I can not chose the specific folder that I want to. Any advice? Many thanks Paulina Welcome to the Mac! This is actually an Apple thing, not an Office thing, so remember it--you might run ...

Saving data to Jpeg or gif
I'm trying to save a large block of Excel data as an image file. I have tried copying the data and pasting into "Paint" and then saving as an image. This works...but some data disappears in the strangest way. Very strange. All of the data gets copied but some is missing when you look at the image. Does anybody know a simple way to save Excel data as an image ? Thanks, Grinch Andy Pope has a free add-in that you can use to export images: http://www.andypope.info/vba/gex.htm Grinch wrote: > I'm trying to save a large block of Excel data as an image file. ...