Loading Multiple files in to one table

• venu.srirangam

  Newbie

  Points: 8

  More actions

  August 29, 2011 at 9:21 am

  #242435

  Hi frnds,,

  I have one issue loading multiple files into one table.

  I have ~100 txt files to be loaded into one table, each file has

  Header record

  Column Names

  Data

  Trailer Record

  Number of columns for all the text files differs, meta data is not equal.

  For example :

  File1 Columns:

  Part Number|Rev|State|Type|Weight|PC Wt Units|Noun Phrase|Noun Phrase Description|Noun Phrase Modifier|End One Fitting Threads|End One Male/Female/Swivel|End One System|End One Tube Size|End Two Fitting Threads|End Two Male/Female/Swivel|End Two System|End Two Tube Size|Material Fitting|Finish

  File 2 columns :

  Part Number|Rev|State|Type|Weight|PC Wt Units|Noun Phrase|Noun Phrase Description|Noun Phrase Modifier|Bore|Bore Type|Dynamic Capacity (kN)|Number of Rows|Number of Seals|Number of Shields|Outside Diameter|Type of Ball Bearing|Width-LC|Linear Dimension Units|Max Housing Fillet Radius|Max Shaft Fillet Radius|Heat Treatment

  So far what i did is...I loaded all the 100 files into one table as a a single column using for each loop.

  Please help me inserting the data into their respective columns.

  Thanks

• Laura_SqlNovice

  SSCrazy

  Points: 2751

  More actions

  August 29, 2011 at 9:33 am

  #1374532

  Hmm in that case you have to read the row which has column names and then parse..

  I think the better way to do is... read first record from each file and create table using that info and then stage data in those tables... and then use sysobjects and sycolumns to create a staging table with all the columns from all the tables.. or you can view all the columns in the tables and select only the ones you need in the integrated table... and then do insert..

• toddasd

  SSCertifiable

  Points: 6298

  More actions

  August 29, 2011 at 9:37 am

  #1374534

  So far what i did is...I loaded all the 100 files into one table as a a single column using for each loop.

  You don't want to do this because now you have to parse the columns. Instead, import the text files into their own tables so that each column in the text file is a column in the table. This is now your staging table. Now it is easy to write an input statement that maps the columns from your staging table to your destination table.

  ______________________________________________________________________________
  How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.