Batch Imports, Importing the file name

Question

Post reply

Batch Imports, Importing the file name

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

It is expecting an END to match the WHILE ... BEGIN, so there is a syntax error on whatever comes before GO.

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 2

Whoops. Sorted. (Kind of)

CREATE procedure usp_ImportMultipleFiles_Leavers_FR @filepath varchar(500),

@pattern varchar(100), @TableName varchar(128)

as

set quoted_identifier off

declare @query varchar(1000)

declare @max1 int

declare @count1 int

Declare @filename varchar(100)

DECLARE @Query2 varchar(1000)

set @count1 =0

create table #x (name varchar(200))

CREATE TABLE #MyTempTable (

UPN nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

Forename nvarchar (255) COLLATE Latin1_General_CI_AS NULL ,

MiddleName nvarchar (255) COLLATE Latin1_General_CI_AS NULL ,

Surname nvarchar (255) COLLATE Latin1_General_CI_AS NULL ,

PreferredNames nvarchar (255) COLLATE Latin1_General_CI_AS NULL ,

DateOfBirth datetime NULL ,

Gender nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

FormerUPN nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

Postcode nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

LEA nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

DFES nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

EntryDate datetime NULL ,

LeavingDate datetime NULL ,

FRFileName datetime NULL ,

DateImported datetime NULL)

set @query ='master.dbo.xp_cmdshell "dir '+rtrim(@filepath)+rtrim(@pattern) +' /b"'

insert #x exec (@query)

delete from #x where name is NULL

select identity(int,1,1) as ID, name into #y from #x

drop table #x

set @max1 = (select max(ID) from #y)

--print @max1

--print @count1

While @count1 <= @max1

begin

set @count1=@count1+1

set @filename = (select name from #y where [id] = @count1)

set @Query ='BULK INSERT '+ #MyTempTable + ' FROM "'+ @Filepath+@Filename+'" WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'',FIRSTROW = 2, FORMATFILE = ''\\ws-74015\changes\changes.fmt'' )'

--print @query

exec (@query)

-- your Bulk Insert inserted into the temp table instead of the permanent table. Now we have to move the result into your @tablename adding @Filepath+@Filename as the last field:

set @query2 ='Insert ' + @tablename +' Select *,' + ''''+ @Filepath+@Filename + '''' + 'from #MyTempTable'

--print @Query2

exec (@query2)

drop table #y

END

GO

Im getting the error - Invalid column name '#MyTempTable', and I cant see anywhere where it is trying to pick this up as a column rather than a table????

thanks again for the above advice!!!!

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Got It. I got the same error message when testing @query line, I uncommented print @query and found where I got it wrong:

Change @query like this:

set

@query ='BULK INSERT #MyTempTable FROM "'+ @Filepath+@Filename+'" WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'',FIRSTROW = 2, FORMATFILE = ''\\ws-74015\changes\changes.fmt'' )'

The difference is that when you use @tablename this is the variable that exists while #MyTempTable is a string so you can include it within the string like

'BULK INSERT #MyTempTable FROM "'

This is just a string composition mistake. Since the string suggestion was mine, I should have tested it before.

Regards,Yelena Varsha

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

That is one reason why I like to use replaceable tags instead of multiple concatenation to build strings. I find it much easier to debug and maintain, especially if you're building a long multiline string with multiple substitutions of the same expression.

set

@query = REPLACE('BULK INSERT #MyTempTable FROM "<file>" WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'',FIRSTROW = 2, FORMATFILE = ''\\ws-74015\changes\changes.fmt'' )',

'<file>', @Filepath+@Filename)

set @query2 = REPLACE(REPLACE('Insert <table> Select *, ''<file>'' from #MyTempTable',

'<table>', @tablename),

'<file>', @Filepath+@Filename)

The REPLACE function will also handle implicit conversions to string for the replacement arguments.

SET @query = REPLACE(REPLACE(' ... <int> ... <now> ... ', '<int>', @int), '<now>', GETDATE())

instead of having CAST functions obscuring the string you're trying to build:

SET @query = ' ... ' + CAST(@int as varchar) + ' ... ' + CAST(GETDATE() as varchar) + ' ... '

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 5

Again, thanks for the help,

I will have a go at putting everything together again a little later on and let you know how it goes!

Debbie

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 6

I have got the stored procedure created now without any errors which is great. However when Im attempting to execute it Im getting the following errors.

Server: Msg 241, Level 16, State 1, Line 1

Syntax error converting datetime from character string.

I thought I would change the table that the data is being imported into to nvarchar to see what would happen and got the following errors.

Server: Msg 8152, Level 16, State 4, Line 1

String or binary data would be truncated. The statement has been terminated.

Server: Msg 208, Level 16, State 1, Procedure usp_ImportMultipleFiles_Leavers_FR, Line 38 Invalid object name '#y'.

The first error Im looking into. It must be a problem with my format file or the fact that my actual table is nvarchar and the temp table I have built is datetime. It is the invalid object name I am unsure about. I have had a look at the stored procedure and this seems fine.

Again any help would be appreciated.

Thanks again

Debbie

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

Debbie,

Sorry, had a busy day yesterday.

I think that the problem with #y may be here. Your Drop Table statement is inside the WHILE loop so after the first pass the table is dropped and the second pass can not find it.

drop table #y

END

Try to change places for these 2 lines.

let me know if it helped.

Regards,Yelena Varsha

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 8

Brilliant,

The error has now been resolved and all Im left with is the error

Server: Msg 213, Level 16, State 5, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

I must admit, Im struggling to get my head round this one because everything seems to match....

The table that the imported records will be put into contains the following columns:

^{UPN, Forename, Middlename, Surname, DateOfBirth, Gender, PreviousUPN}

LEACode, DfESCode, StartDate, ToDate, DateImported, FRFileName

#MyTempTable in the SP contains the following columns:

^{UPN, Forename, Middlename, Surname, DateOfBirth, Gender, PreviousUPN}

LEACode, DfESCode, StartDate, ToDate, DateImported, FRFileName

The format file contains the following columns:

^{UPN, Forename, Middlename, Surname, DateOfBirth, Gender, PreviousUPN}

LEACode, DfESCode, StartDate, ToDate, DateImported, FRFileName

The source file contains the following columns:

^{UPN, Forename, Middlename, Surname, DateOfBirth, Gender, PreviousUPN}

LEACode, DfESCode, StartDate, ToDate.

And I have made sure that the format files show that the last 2 SQL columns are not in the source data:

^8.0

11

^{------------------------------}

^{12 SQLCHAR 0 0 "" 12 DateImported Latin1_General_CI_AS}

13 SQLCHAR 0 0 "" 13 FRFileName Latin1_General_CI_AS

Im wondering if I have used the wrong table struncture for #MyTempTable or whether the format file is incorrect.

Thanks again for the advice

Debbie

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

If you want a bulk insert to ignore some table columns that don't appear in the input file, the format file must have 0 for prefix length, column length, and column ordinal. You could either change the 12 and 13 in the 6th column of the format file to 0, or just delete the two rows from the format file.

See "Using a format file to skip a table column" in BOL.

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 10

Hi again.

Still working on this one, been snowed under at work so I havent had much time to focus on it but reading the last comment quickly, checked my Format file at 12 and 13 are set to 0.

12 SQLCHAR 0 0 "" 12 DateImported Latin1_General_CI_AS

13 SQLCHAR 0 0 "" 13 FRFileName Latin1_General_CI_AS

The top of the format file says

8.0

11

So its ignoring columns 12 and 13 too?

I just know Im missing something really silly somewhere. Need to spend a decent amount of time figuring this one out!

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 11

The second row of the format file should be the number of input fields (including skipped fields), or in other words the number of rows remaining in the format file. It is not the number of output columns.

The sixth column of the format file is the column ordinal value that specifes the destination column, You show two rows with a field width of 0 and non-zero output column numbers. The rough translation of this is "Pull a column out of thin air and store it in output column 12 which doesn't exist. Then do it again." What you want to do is put the field width in column 4, and a 0 in column 6 to indicate that this portion of the record is ignored.

If you want to skip over, for example, 10 columns of the input record use this line:

12 SQLCHAR 0 10 "" 0 SKIP Latin1_General_CI_AS

The field name "SKIP" has no functional meaning, you could use "DateImported" or anything you like. It is just there to make the file a little more human-readable.

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 12

Aaaaaah I see. Right, Ill have a go at this today and let you know how I get on.

Again thank you for all the help!

Debbie

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 13

One other thing, the last row of the format file should have the end-of-line sequence in the field terminator column (column 5). For example:

12 SQLCHAR 0 10 "\r\n" 0 SKIP Latin1_General_CI_AS

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 14

Still no luck. Im getting the same error message. I have managed to get format files working before so I know it can be done

Here is an example of another one I managed to get working (Middlename, DateSent and DateImported are not in the source file.)

8.0

13

1 SQLCHAR 0 255 "," 1 UPN Latin1_General_CI_AS

2 SQLCHAR 0 255 "," 2 Forename Latin1_General_CI_AS

3 SQLCHAR 0 0 "" 0 Middlename Latin1_General_CI_AS

4 SQLCHAR 0 255 "," 4 Surname Latin1_General_CI_AS

5 SQLCHAR 0 255 "," 5 PreferredNames Latin1_General_CI_AS

6 SQLCHAR 0 255 "," 6 DateOfBirth Latin1_General_CI_AS

7 SQLCHAR 0 10 "," 7 Gender Latin1_General_CI_AS

8 SQLCHAR 0 255 "," 8 FormerUPN Latin1_General_CI_AS

9 SQLCHAR 0 255 "," 9 Postcode Latin1_General_CI_AS

10 SQLCHAR 0 20 "," 10 LEA Latin1_General_CI_AS

11 SQLCHAR 0 20 "," 11 DFES Latin1_General_CI_AS

12 SQLCHAR 0 10 "," 12 EntryDate Latin1_General_CI_AS

13 SQLCHAR 0 255 "\r\n" 13 LeavingDate Latin1_General_CI_AS

14 SQLCHAR 0 0 "" 14 DateSent Latin1_General_CI_AS

15 SQLCHAR 0 0 "" 15 DateImported Latin1_General_CI_AS

Think Im going to have to call time on this one. Its beyond my skills. Thanks for helping though!

Debbie

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 15

Hi Debbie,

I am watching the discussion but can not give any input on format file because I did not work much with format files and BCP. The only thing I may mention, it may not be a design issue but data issue. For example we have a third-party import tool that imports CSV files to an application database. When there is a comma in the field data then the tool interprets it as a next field so the number of fields don't much for this record and for this particular record we are getting an error in the log:

Error message:

Record Number 1 you are importing has a different number

of fields than the number of fields defined in the header

of the CSV file

The same procedure works fine for all other records in the file.

Regards,Yelena Varsha