![]() |
Automate Excel export to a CSV file
Hi...
I'm wondering if it's possible to export an excel file to a csv file via script. I have data in excel that I need to import into a SQL Server database, but importing the excel spreadsheet results in missing data or values that are assigned to null when they have a value in the spreadsheet. If I convert the excel spreadsheet to a csv and import the csv, it works fine. I know the users of the spreadsheet can export/save as a csv file, I'd rather automate this process rather than relying on them to actually do this themselves. Is this at all possible? -- Chris Burke |
Automate Excel export to a CSV file
Yes you can. I have data in two columns, A has field names and B has values
and use this code which is a modified version of something I got off this forum: ' Write data to CSV file Set fswrite = CreateObject("Scripting.FileSystemObject") 'Set path names CSVPathName = FPath + WriteFileName + ".csv" DocPathName = FPath + WriteFileName + ".doc" ' Open and export data to CSV File fswrite.CreateTextFile CSVPathName Set fwrite = fswrite.GetFile(CSVPathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) LastRow = z + 1 LastCol = 2 With Sheets("Data Table2") For ColCount = 1 To LastCol OutputLine = "" For RowCount = 2 To LastRow If OutputLine = "" Then OutputLine = Cells(RowCount, ColCount).Value 'MsgBox "OutputLine = " & OutputLine Else 'If ColCount = 2 Then MsgBox RowCount & ": " & Cells(RowCount, ColCount).Value OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount).Value End If Next RowCount tswrite.writeline OutputLine Next ColCount End With tswrite.Close MsgBox "CSV has been written!" -- Hope it helps, Andy "Chris B" wrote: Hi... I'm wondering if it's possible to export an excel file to a csv file via script. I have data in excel that I need to import into a SQL Server database, but importing the excel spreadsheet results in missing data or values that are assigned to null when they have a value in the spreadsheet. If I convert the excel spreadsheet to a csv and import the csv, it works fine. I know the users of the spreadsheet can export/save as a csv file, I'd rather automate this process rather than relying on them to actually do this themselves. Is this at all possible? -- Chris Burke |
All times are GMT +1. The time now is 11:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com