Parse CSV data for SQL Insert Query Using Regex

Question

I need help with some Regex, please. I'm trying to use Integromat to get some data, then parse it into a format suitable for a SQL INSERT command. Integromat uses Javascript regex.

The initial data looks like this:

Identifier,Handle,Type
CS18511,big-3-jersey,men's jerseys
CS185110231,big-3-jersey,men's jerseys
CS185110232,big-3-jersey,men's jerseys
CS185110233,big-3-jersey,men's jerseys

I want to apply regex substitution to achieve this format, ideally without the heading row.

('CS18511','big-3-jersey,men''s' jerseys'),
('CS185110231','big-3-jersey,men''s jerseys'),
('CS185110232','big-3-jersey,men''s jerseys'),
('CS185110233','big-3-jersey,men''s jerseys')

I've tried the below, which seems to group the items I want, but can't find a substitution that works to create the desired output.

((?=[^\s,])(?: ?[^[\s,]+|\[[^]]*])*|(?<=,|^)(?=\s*(?:,|$)))

Any help much appreciated!

Cheers

Why???? You can insert the data directly with BULK INSERT

Panagiotis Kanavos
– Panagiotis Kanavos

2021-11-17 17:40:14 +00:00
Commented Nov 17, 2021 at 17:40 — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 17, 2021 at 17:40

John Cappelletti · Accepted Answer · 2021-11-17 17:51:10Z

1

Here is a working example on how to use BULK INSERT

Create Table #YourTable (Identifier varchar(50),Handle varchar(50),Type varchar(50))
Bulk Insert  #YourTable FROM 'C:\Working\MyData.csv' WITH ( FIRSTROW = 2,
                                                            FIELDTERMINATOR = ',', 
                                                            ROWTERMINATOR = '\n' 
                                                          )
Select * from #YourTable

Results

Identifier   Handle         Type
CS18511      big-3-jersey   men's jerseys
CS185110231  big-3-jersey   men's jerseys
CS185110232  big-3-jersey   men's jerseys
CS185110233  big-3-jersey   men's jerseys

answered Nov 17, 2021 at 17:51

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tbizzlebozzle Over a year ago

Many thanks John... I wasn't aware of bulk insert. Unfortunately, the CSV data is created dynamically via an API call to our PIM, so it's not a physical file with a location to reference.

tbizzlebozzle Over a year ago

This is the failed output:

CREATE TABLE PIM_DATA (     Identifier VARCHAR(100),     Handle VARCHAR(100),     Type VARCHAR(100)  ) END BEGIN BULK INSERT PIM_DATA FROM '"Identifier,Handle,Shopify Type CS18511,big-3-jersey,men's jerseys CS185110231,big-3-jersey,men's jerseys CS185110232,big-3-jersey,men's jerseys etc...

John Cappelletti Over a year ago

@tbizzlebozzle bulk insert is assume a source file.

Collectives™ on Stack Overflow

Parse CSV data for SQL Insert Query Using Regex

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related