Create a SQL Server table from text file

Question

I need to find a way to create a table from a text file that contains the field definitions (around 100 or more) of what my table structure should look like. Here is a sample of fields names with size and description:

da_ctat   1 progress status of a sample
vc_acct   6 Assigned account
qc_bact   6 billing account #1
lc_bac2   6 billing account #2 for a split
...
...
zc_bar   12 z code
pc_wav    1 wai
oc_p1     1 ftp1
tc_df1    1 ftp2
kc_qq     1 ztp
mc_split  1 split billing

Here is what the above text means

column 1-8 character is the field name
column 9 space 
column 10-11  is the size of field name
column 12 space 
column 13 - 80 is field comment

I need to create a stored procedure or find some other way that will create a table with the fields show above. For example, a field name da_ctat that is 1 character long and description "progress status of a sample". The next field in the table called vc_acct, 6 characters long and description of "Assigned account", etc...a

Since I have over hundred fields to create, is there a way to write a stored procedure to create this table structure without me having to manually create the fields for a table?

thanks for your assist community Nick

from the file how would you know the table name, column data types, primary keys etc? — T I
– T I, Commented Apr 23, 2013 at 15:26
Table name needs to be called Results. All the fields are characters and da_ctat can be unique key. — Nick
– Nick, Commented Apr 23, 2013 at 15:48

swasheck · Accepted Answer · 2013-04-23 16:19:04Z

Probably the most direct way to do this would be to create an SSIS package with a fixed width Flat File as a data source. I'd set your destination as something notably different than your final table will be (i.e. use a "rawdata" schema or even a separate database named "rawdata"). You could use this initial mapping to create the table with suggested data types (but you really need to make sure you know your data).

SSIS

Once you've done this, you could create a ForEach Loop container in SSIS that iterates through a specific folder that has these flat file sources and your raw destination. If this is a one-time thing, you can do this by installing SQL Server Data Tools to your current Visual Studio 2010 installation (if you have one), or the installer will install SSDT within VS 2010 Shell if you don't have it. If this isn't a one-off, then you're going to need to look into your licensing for installing SSIS in order to deploy this as a package that you can schedule to run periodically.

Failing all that, you could generate the table schema by opening SSMS, right-clicking your target database, clicking Tasks, Import Data, choose Flat File source, fixed width destination, mapping your source (you're going to go through the pain of creating these columns, if you really have > 100), and then generating a create table script.

However, if you really only have those three columns ...

CREATE TABLE schema rawdata AUTHORIZATION <pick an owner here>;
CREATE TABLE rawdata.rawfields (
    fieldname varchar(8), -- i'm actually unclear here because 
                          -- the next column's name/purpose is unclear
    fieldsize int, -- or other numeric datatype 
    fieldcomment varchar(255)
);

Then you could write a quick C# or PowerShell script to iterate a directory with the files. This would look something like:

C#

string fieldname, fieldsize, fieldcomments;            
var files = System.IO.Directory.GetFiles("path", "*.ext");
foreach (var file in files)
{
    var lines = System.IO.File.ReadAllLines(file);
    foreach (var line in lines)
    {
        fieldname = line.Substring(0, 8);
        fieldsize = line.Substring(9, 1);
        fieldcomments = line.Substring(10, 73);
    }
}

From here, I'd set up a connection to the SQL Server instance, create a parameterized insert command and insert the data in the variables.

BULK INSERT

Create a format file:

<?xml version="1.0"?>
<BCPFORMAT
       xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharFixed" LENGTH="8"/>
    <FIELD ID="2" xsi:type="CharFixed" LENGTH="2"/>
    <FIELD ID="3" xsi:type="CharFixed" LENGTH="68"/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n"
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="fieldname" xsi:type="SQLCHAR" />
    <COLUMN SOURCE="2" NAME="fieldlength" xsi:type="SQLCHAR" />
    <COLUMN SOURCE="3" NAME="fieldcomments" xsi:type="SQLCHAR" />
  </ROW>
</BCPFORMAT>

then you can write up a PowerShell script or a C# app to iterate the files in the directory (as above) and call (assuming you can gain a trusted connection)

bcp <<yourdatabase>>.rawdata.rawfiles in \\path\to\data\file.ext 
         -f \\path\to\format\file.Xml -T

otherwise

bcp <<yourdatabase>>.rawdata.rawfiles in \\path\to\data\file.ext 
         -f \\path\to\format\file.Xml -U username -P password

marceljg · Accepted Answer · 2013-04-23 15:33:31Z

2

Is this a one-time effort, or something that needs to be automated?

For one-time, I would use excel and lots of concatenation. 100 lines really is not much to hand format.

For repeated, I would preprocess this file in your scripting language of choice to turn it into a standard SQL Script. My choice would be powershell since it integrates very easily with sql server.

The definition file seems incomplete as there is no indication of the field type. Are they all ints, floats, varchar?

answered Apr 23, 2013 at 15:33

marceljg

1,0677 silver badges14 bronze badges

2 Comments

marceljg Over a year ago

I must say though, that I hope those field names are made-up sample data, as otherwise the naming method is terrible. You may wish also to look up normalizing data as well.

Nick Over a year ago

This is a one time thing. The field types are all characters. Unfortunately, the data we are receiving from name their fields this way.

Collectives™ on Stack Overflow

Create a SQL Server table from text file

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related