| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating CSV Comma Delimited File
I am using the following code to create a CSV file for a sheet within a
workbook. I have posted the CSV file that gets created and there are 2 problems with the file. The first is that there is an extra comma placed at the end of each row. In some cases the comma is there, and in other cases it is not there (although for the sample I've provided the extra column is there for each row). Secondly, there is a group of commas at the end of the file. I think this situation can be eliminated if I specify the actual range for which I want to create the CSV file. Does anyone know the syntax for that? Thanks. Connie ' Create CSV file for Technicians Sheets("Field Rep Time Sheet").Select Directory = CurDir FName = "\Upload Tech " & Format(Now(), "yyyymmmddhhmm") FileName = Trim(Directory) + Trim(FName) Sheets("Upload Data Tech").Copy Set wb = ActiveWorkbook wb.SaveAs FileName & ".csv", FileFormat:=xlCSV wb.Close SaveChanges:=False MsgBox "Upload File Saved for TECHNICIANS" Here is the data in the worksheet. The last column is column O and is either 20 or 0. EndDate EmployeeName OracleID TechNo TotalHours TrainingHours TechOffice VacationHours VacationEndDate BereavementHours JuryDutyHours ClericalHours WarehouseHours TotalHours PerDiem 10/7/2006 Michael Albert 23709 1234 7.00 4.00 0.00 0.00 0.00 0.00 0.00 0.00 11.00 20 10/12/2006 Michael Albert 23709 1234 1.92 0.00 4.10 5.70 10/12/2006 0.00 0.00 0.00 0.00 11.72 0 10/13/2006 Michael Albert 23709 1234 0 1.916666667 4.1 0 5.466666667 0 0 0 11.48333333 0 10/7/2006 Michael Albert 23709 1234 7 4 0 0 0 0 0 0 11 20 10/8/2006 Michael Albert 23709 1234 5.85 0 4.1 0 0 0 0 0 9.95 0 Here is the data in the CSV file. Notice that there is an extra comma at the end of each row, and there are commas at the end of the file. The commas at the end of the file actually extend to I believe 65000 rows. EndDate,EmployeeName,OracleID,TechNo,TotalHours,Tr ainingHours,TechOffice,Va*cationHours,VacationEndD ate,BereavementHours,JuryDutyHours,ClericalHours,W a*rehouseHours,TotalHours,PerDiem,, 10/7/2006,Michael Albert,23709,1234,7.00,4.00,0.00,0.00,,0.00,0.00,0 .00,0.00,11.00,20,, 10/12/2006,Michael Albert,23709,1234,1.92,0.00,4.10,5.70,10/12/2006,0.00,0.00,0.00,0.00,11.72,*0,, 10/13/2006,Michael Albert,23709,1234,0,1.916666667,4.1,0,,5.466666667 ,0,0,0,11.48333333,0,, 10/7/2006,Michael Albert,23709,1234,7,4,0,0,,0,0,0,0,11,20,, 10/8/2006,Michael Albert,23709,1234,5.85,0,4.1,0,,0,0,0,0,9.95,0,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating CSV Comma Delimited File
Check your other post.
Connie wrote: I am using the following code to create a CSV file for a sheet within a workbook. I have posted the CSV file that gets created and there are 2 problems with the file. The first is that there is an extra comma placed at the end of each row. In some cases the comma is there, and in other cases it is not there (although for the sample I've provided the extra column is there for each row). Secondly, there is a group of commas at the end of the file. I think this situation can be eliminated if I specify the actual range for which I want to create the CSV file. Does anyone know the syntax for that? Thanks. Connie ' Create CSV file for Technicians Sheets("Field Rep Time Sheet").Select Directory = CurDir FName = "\Upload Tech " & Format(Now(), "yyyymmmddhhmm") FileName = Trim(Directory) + Trim(FName) Sheets("Upload Data Tech").Copy Set wb = ActiveWorkbook wb.SaveAs FileName & ".csv", FileFormat:=xlCSV wb.Close SaveChanges:=False MsgBox "Upload File Saved for TECHNICIANS" Here is the data in the worksheet. The last column is column O and is either 20 or 0. EndDate EmployeeName OracleID TechNo TotalHours TrainingHours TechOffice VacationHours VacationEndDate BereavementHours JuryDutyHours ClericalHours WarehouseHours TotalHours PerDiem 10/7/2006 Michael Albert 23709 1234 7.00 4.00 0.00 0.00 0.00 0.00 0.00 0.00 11.00 20 10/12/2006 Michael Albert 23709 1234 1.92 0.00 4.10 5.70 10/12/2006 0.00 0.00 0.00 0.00 11.72 0 10/13/2006 Michael Albert 23709 1234 0 1.916666667 4.1 0 5.466666667 0 0 0 11.48333333 0 10/7/2006 Michael Albert 23709 1234 7 4 0 0 0 0 0 0 11 20 10/8/2006 Michael Albert 23709 1234 5.85 0 4.1 0 0 0 0 0 9.95 0 Here is the data in the CSV file. Notice that there is an extra comma at the end of each row, and there are commas at the end of the file. The commas at the end of the file actually extend to I believe 65000 rows. EndDate,EmployeeName,OracleID,TechNo,TotalHours,Tr ainingHours,TechOffice,Va*cationHours,VacationEndD ate,BereavementHours,JuryDutyHours,ClericalHours,W a*rehouseHours,TotalHours,PerDiem,, 10/7/2006,Michael Albert,23709,1234,7.00,4.00,0.00,0.00,,0.00,0.00,0 .00,0.00,11.00,20,, 10/12/2006,Michael Albert,23709,1234,1.92,0.00,4.10,5.70,10/12/2006,0.00,0.00,0.00,0.00,11.72,*0,, 10/13/2006,Michael Albert,23709,1234,0,1.916666667,4.1,0,,5.466666667 ,0,0,0,11.48333333,0,, 10/7/2006,Michael Albert,23709,1234,7,4,0,0,,0,0,0,0,11,20,, 10/8/2006,Michael Albert,23709,1234,5.85,0,4.1,0,,0,0,0,0,9.95,0,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating CSV Comma Delimited File
Here is the reason for your extra commas when excel saved the worksheet
as a CSV file it saw some blank cells in your used range here's a quick example: In excel I typed this A B C D E 1 1 2 1 2 3 1 2 3 4 1 2 3 4 5 1 2 3 4 5 then saved it as a CSV file I opened the CSV file in wordpad (or notepad) and this is what it shows 1,,,, 1,2,,, 1,2,3,, 1,2,3,4, 1,2,3,4,5 the commas are separating the blank values in the remaining cells in the used range Sandy Connie wrote: I am using the following code to create a CSV file for a sheet within a workbook. I have posted the CSV file that gets created and there are 2 problems with the file. The first is that there is an extra comma placed at the end of each row. In some cases the comma is there, and in other cases it is not there (although for the sample I've provided the extra column is there for each row). Secondly, there is a group of commas at the end of the file. I think this situation can be eliminated if I specify the actual range for which I want to create the CSV file. Does anyone know the syntax for that? Thanks. Connie ' Create CSV file for Technicians Sheets("Field Rep Time Sheet").Select Directory = CurDir FName = "\Upload Tech " & Format(Now(), "yyyymmmddhhmm") FileName = Trim(Directory) + Trim(FName) Sheets("Upload Data Tech").Copy Set wb = ActiveWorkbook wb.SaveAs FileName & ".csv", FileFormat:=xlCSV wb.Close SaveChanges:=False MsgBox "Upload File Saved for TECHNICIANS" Here is the data in the worksheet. The last column is column O and is either 20 or 0. EndDate EmployeeName OracleID TechNo TotalHours TrainingHours TechOffice VacationHours VacationEndDate BereavementHours JuryDutyHours ClericalHours WarehouseHours TotalHours PerDiem 10/7/2006 Michael Albert 23709 1234 7.00 4.00 0.00 0.00 0.00 0.00 0.00 0.00 11.00 20 10/12/2006 Michael Albert 23709 1234 1.92 0.00 4.10 5.70 10/12/2006 0.00 0.00 0.00 0.00 11.72 0 10/13/2006 Michael Albert 23709 1234 0 1.916666667 4.1 0 5.466666667 0 0 0 11.48333333 0 10/7/2006 Michael Albert 23709 1234 7 4 0 0 0 0 0 0 11 20 10/8/2006 Michael Albert 23709 1234 5.85 0 4.1 0 0 0 0 0 9.95 0 Here is the data in the CSV file. Notice that there is an extra comma at the end of each row, and there are commas at the end of the file. The commas at the end of the file actually extend to I believe 65000 rows. EndDate,EmployeeName,OracleID,TechNo,TotalHours,Tr ainingHours,TechOffice,Va*cationHours,VacationEndD ate,BereavementHours,JuryDutyHours,ClericalHours,W a*rehouseHours,TotalHours,PerDiem,, 10/7/2006,Michael Albert,23709,1234,7.00,4.00,0.00,0.00,,0.00,0.00,0 .00,0.00,11.00,20,, 10/12/2006,Michael Albert,23709,1234,1.92,0.00,4.10,5.70,10/12/2006,0.00,0.00,0.00,0.00,11.72,*0,, 10/13/2006,Michael Albert,23709,1234,0,1.916666667,4.1,0,,5.466666667 ,0,0,0,11.48333333,0,, 10/7/2006,Michael Albert,23709,1234,7,4,0,0,,0,0,0,0,11,20,, 10/8/2006,Michael Albert,23709,1234,5.85,0,4.1,0,,0,0,0,0,9.95,0,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating CSV Comma Delimited File | Excel Discussion (Misc queries) | |||
| Importing Comma delimited file | Excel Discussion (Misc queries) | |||
| Saving multi-tab excel file created from comma delimited text file | Excel Programming | |||
| How can I save a file as a comma-delimited text file in Excel? | Excel Discussion (Misc queries) | |||
| Comma Delimited Text File Issue | Excel Programming | |||
Linear Mode
