opkium.blogg.se

'Excel 8.0 Database=C:\ImportData.xls', 'SELECT * FROM ') 'Excel 8.0 Database=C:\ImportData.xls', ) IF OBJECT_ID('dbo.SampleCSVTable') IS NOT NULL

PRINT 'Error on row ' + CONVERT(VARCHAR, + ': ' + ERROR_MESSAGE()īcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName , CHAR(130), '"') -Re-add quotation markįROM STRING_SPLIT((SELECT attdata FROM #csvRows WHERE row = ',') , CHAR(128), CHAR(10)) -Re-add the line break Split CSV string into columns for each row SELECT = + 'c' + CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY value)) + ' NVARCHAR(MAX),'įROM STRING_SPLIT((SELECT attdata FROM #csvRows WHERE row = 1), ',') INSERT INTO #csvRows SELECT value FROM CHAR(10))ĭELETE FROM #csvRows WHERE attdata = '' OR attdata = ',' -remove blank rowĪLTER TABLE #csvRows ADD row INT IDENTITY(1,1)ĭECLARE NVARCHAR(MAX)= 'ALTER TABLE #csvTable ADD ' Re-concatenate string the old fashioned way Stuff and string_agg seem to be very unreliable. WHERE row % 2 = 0 -Every other row will be a quoted column (i.e.

, ',', CHAR(129)) -Replace commas on quoted lines with temp character , CHAR(10), CHAR(128)) -Replace LF on quoted lines with temp character UPDATE SET m_text = REPLACE(REPLACE(m_text The rows preceding and following will be unquoted INSERT INTO SELECT value FROM '"') - Each quoted column will be on a separate row. SELECT = '""', CHAR(130)) -Double quotes represent an escaped quotation mark (a quotation mark used within a cell) Separate lines that are surrounded by quotes , CHAR(10) + CHAR(10), CHAR(10)) -Replace LF LF with LF , CHAR(13), CHAR(10)) -Windows line ending is CR LF remove CR and keep LF for consitency across OS SELECT = CHAR(239), '') -Some funky character at the beginning of most CSV strings pulled from Excel INSERT INTO #import EXEC sp_CSVtoTable Now you're ready to do whatever you need with the data (insert into permanent table, etc.) In your procedure, call this procedure and insert the restults. In your procedure, create your temporary table based on the CSV data (must matchģ. In your procedure, get the text from the file (or whatever your source is). This assumes you will create this procedure exactly (so you can reuse it) and build a separate import procedure. Replace any LF (line feed) or comma characters that are in a cell so that we can correctlyĬHAR(13) = CR (carraige return/end of line)ĬHAR(128) = Represents line feed within quoted columnĬHAR(129) = Respesents comma within quoted columnĬHAR(130) = Represents quoted text within quoted columnġ. The CSV isn't as simple as splitting on comma and end-of-line. CSV text exported from Excel allows cells to contain commas and line breaks, so splitting