0
Hello. I get *.CSV files from a program that measures ink tint. If the ink is solid I get a file with 15 items (spetrum at 45°angle), if it is metallic with 36 items (Spectrum at 25°, 45°, and 75°angles). They have in common the 45°angle information. I’m trying to do a BULK INSERT on SQLSERVER with these CSV’s. When the CSV file has both types of inks, my table is populated correctly, because the fields of the solid ink without information return NULL. When the file has only the metallic ink the table is also populated correctly, because it has all the information. But when the file has only solid ink my table is out of order.
The first line of the CSV always displays the name of the columns. Is there any way to select the correct column in SQL SERVER BULK INSERT?
CREATE TABLE TB_SERIES(
[HDR_ID] [varchar](50) NULL,
[CKZ_NAME] [varchar](50) NULL,
[TimeStamp] [varchar](50) NULL,
[Model] [varchar](50) NULL,
[ColorName] [varchar](50) NULL,
[Paintline] [varchar](50) NULL,
[Param4] [varchar](50) NULL,
[Param5] [varchar](50) NULL,
[dL* 25] [varchar](50) NULL,
[da* 25] [varchar](50) NULL,
[db* 25] [varchar](50) NULL,
[dC* 25] [varchar](50) NULL,
[dH* 25] [varchar](50) NULL,
[dL* 45] [varchar](50) NULL,
[da* 45] [varchar](50) NULL,
[db* 45] [varchar](50) NULL,
[dC* 45] [varchar](50) NULL,
[dH* 45] [varchar](50) NULL,
[dL* 75] [varchar](50) NULL,
[da* 75] [varchar](50) NULL,
[db* 75] [varchar](50) NULL,
[dC* 75] [varchar](50) NULL,
[dH* 75] [varchar](50) NULL,
[dEe Mean] [varchar](50) NULL,
[dSparkle_area 15] [varchar](50) NULL,
[dSparkle_area 45] [varchar](50) NULL,
[dSparkle_area 75] [varchar](50) NULL,
[dSparkle_int 15] [varchar](50) NULL,
[dSparkle_int 45] [varchar](50) NULL,
[dSparkle_int 75] [varchar](50) NULL,
[dSparkle 15] [varchar](50) NULL,
[dSparkle 45] [varchar](50) NULL,
[dSparkle 75] [varchar](50) NULL,
[dE AUDI 25] [varchar](50) NULL,
[dE AUDI 45] [varchar](50) NULL,
[dE AUDI 75] [varchar](50) NULL
) ON [PRIMARY]
BULK INSERT DB_Tonalidade.dbo.TB_SERIES
FROM 'C:\Desktop\Medições Tonalidade\Color_20-06-03 14_13_06.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 3
)
Thank you.
In the text file (csv format) the column indicating the type of ink (solid or metallic) is always in the same position in all cases?
– José Diz
Yes, they are always in the same position. In any case.
– Robson Goularte
One option is to import the contents of the text file (CSV format) to a temporary table with a single text column. That is, each record of the file is a row in the table, all the contents of the record in a single column. After importing, for each row of the temporary table one should then analyze the field indicating the ink type and do the separation of fields according.
– José Diz
Thanks for the feedback. I will make suggestion.
– Robson Goularte
Oops! There is a flaw in my suggestion. Is there any way to know when the file has only solid ink? Otherwise, before it will be necessary to analyze whether the first row in the temporary table has 15 or 36 items, which will allow to define which was the imported structure.
– José Diz
Yes, it is possible. I think analyzing by the size of the line is better, because new inks can be inserted into the system, either solid or metallic. I also found a solution using SSIS. Thank you very much for the return.
– Robson Goularte
@José Diz, thank you so much for the tip. I finished the project, checking for the size of the line.
– Robson Goularte
Great news. I found the case interesting and later I will add something similar in the article Bulk import of data - https://portosql.wordpress.com/2018/08/12/importacao-de-dados-em-massa/
– José Diz
Excellent article! Power bi will be one of the final destinations for this case. I saved the pdf to study library. Thank you.
– Robson Goularte