March 24, 2023 at 12:52 pm
Well, I hope the title explains what it is. Currently, I have the following that I have done to get CSV data into my table.
BULK INSERT [Pxxx_Sxxx].[dbo].[Audit_Transactions]
FROM 'C:\Users\richa\Downloads\MySQL\xxxx\2023-02-WXYZ.csv'
WITH (
FORMAT = 'CSV'
,FIELDTERMINATOR = ','
,FIRSTROW = 2
,ERRORFILE = 'C:\Users\richa\Downloads\MySQL\xxxx\Error Logs\Errors-01.csv'
,KEEPNULLS
);
However, within my table is a column at the start called [Data_Month] which is where I will be wanting to load in as part of the import at the start. In this example, it would be 2023-02. I'm unable to get the CSV file amended to include this as the start and if I don't have it, when it comes to importing the columns will go in wrongly and cause issues and more likely fail with the import.
Previously I was adding this into my query as a formula but feel having a hard-coded value put in would be much more beneficial.
Thanks, Richard...
March 24, 2023 at 1:07 pm
Realised I can do this in Notepad++ before I import.
Press Ctrl+H to bring up the Find/Replace Dialog.
Choose the Regular expression option near the bottom of the dialog.
To add a word, such as test, at the beginning of each line:
Type ^ in the Find what textbox
Type 2023-02, in the Replace with textbox
Place cursor in the first line of the file to ensure all lines are affected
Click Replace All button
Thought I'd add that in here in case anyone else needs it. Of course, if you have a way to do this without having to amend the CSV I would still like to know.
Thanks, Richard...
March 24, 2023 at 4:29 pm
insert into a temp table , then select from temp table and insert into final, and on this select you add your required date column
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply