0

I have been looking for quite a while for an answer to this and I am amazed it does not seem to be readily available. I have a large number of CSV files. Over 100,000 in fact the last line has "end of report" as the second column and does not have the same number of columns as the rest. If I set lastrow = to the numeric value of my last row the code works fine. Imports into the SQL table with no problem. Otherwise it errors out with an end of file error 5832 I think.

I am at the point of having to write a utility to process the CSV file but surely there is a better answer. Does anyone know how to just discard the last line? I have tried try-catch, error files and so on nothing seems to work. I would think in processing text files this would be a common requirement but so far that does not appear to be so.

Mike O
  • 1
  • Bulk insert how? Which tool/command? What settings? Importing and transforming data is the job of SSIS, either when used as a service or through the Import Data wizard. You can even connect to Hadoop and Spark if you want. bcp and the `BULK INSERT` are *not* meant to import arbitrary CSVs, they are meant to import well-formed files generated with a specific format. – Panagiotis Kanavos Mar 30 '17 at 07:08
  • To put this in context - I load transaction files with from banks, which may or may not have column headers headers, with or without multiple multiple finish lines, arbitrary headers or footers, through SSIS. You can start with the `Import Data Wizard` and set the settings you need for headers, first/last rows etc. Instead of running the ETL script, you can save it locally and use SSDT to edit it, eg to add columns, transform/parse values, lookup data – Panagiotis Kanavos Mar 30 '17 at 07:11
  • BULK INSERT [NEMDispatchSCADA] FROM 'F:\AEMO\CSV\Test\2016\Daily_Dispatch\PUBLIC_DISPATCHSCADA_201601010010_0000000267398502.CSV' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0A', FIRSTROW = 3 ) in MMS it is well formed and imports is there no way to discard the last row? I will investigate SSIS and the import data wizard thanks. – Mike O Mar 30 '17 at 07:54
  • Preprocessing the csv files is simple. You can do it with a simple sed oneliner. Something like this, for instance `sed -i '/^ *end of report *$/d' *.csv` – Håken Lid Mar 30 '17 at 08:30
  • I opted for using sed simpler than using SSIS I found. So thanks all my 30 million row table is now comple. – Mike O Mar 31 '17 at 10:09

0 Answers0