Importing data from a text file to a table

  • [font="Tahoma"]

    Hello Friends,

    I am having a text file with the column names and rows. However, the file is not a delimited text file. It does not have a proper format. Hence i am finding it difficult to configure the Flatfile Source to map the columns.

    Nothing is formatted in the text file. In some place, single space is used between columns and in some other place multiple spaces are used between columns.

    Is there a way to import the information from these kind of text files to a table is SQL Server.

    Please help me on this.

    [/font]

  • If the columns aren't delimited, are they fixed width? In other words, does each column begin at a specfied place on the line? If your file isn't delimited or fixed width then I think you will need to clean your data before you can import it.

    John

  • [font="Arial"]

    Thanks for your response John.. As i had mentioned the file is not properly formatted. The columns are not seperated using fixed width. They are seperated by varying width.

    What shall be done in this case. I believe it would not be possible for us to format the file manually when we have 1000s of records.. Isn't it???

    [/font]

  • Well, if spaces occur only between columns, and not within them, I suppose you could do a find and replace, and put an appropriate delimiter in place of all spaces. You'd need to find some way of doing that so that each block of spaces, rather than each individual spaces, is replaced by a delimiter. If you could post an example of the data in your file, with any sensitive data anonymised or removed, then we may be able to make some suggestions for you.

    John

  • [font="Tahoma"]

    Hello John,

    PFB the sample data that i have in the file.

    Department SubDept EMP NO Primary section Section they work in Salary Grade Salary $(divide 100) Hrs regular emp(1) or manager (6) WEEK-END Date DATE (prev week)

    HR BPO 123456789 John, Mclaine M 5420 2356 D 00000057500 000001200 1 110325

    [/font]

  • [font="Tahoma"]

    Hello John,

    PFA for the exact format of the file. The content did not get copied in the exact manner as it is in the file. Hence i have attached it.

    [/font]

  • You've only sent one row, so I can't see what repeating patterns there are. What I can see in the row you pasted is that each column is separated from the next by a single space. The problem is that the column that appears to be for the employee name appears to include a space. What you could do is to separate out the first name from the last by removing the comma and add an extra column to your table. Get rid of the header row from your file because it has spaces in it that make it more confusing than helpful.

    John

  • [font="Tahoma"]

    Removing the header and adding it manually seems to be a better option. Thanks for your help..:-)

    [/font]

  • As I said before, one row isn't particulary useful. And the header row is totally useless since I can't tell where one column name ends and the next begins. Here's what I'd do:

    (1) Get rid of the header row

    (2) Use Find and replace to replace all instances of a comma followed by a space with just a comma (replace ", " with ",")

    (3) Import each row into a single-column staging table

    (4) Use this excellent article by Jeff Moden to replace the spaces with the delimiter of your choice: http://www.sqlservercentral.com/articles/T-SQL/68378/

    (5) Search this site for another article by Jeff Moden on splitting delimited strings and use that as a basis for transferring the data from your staging table into your destination table.

    Good luck

    John

Viewing 9 posts - 1 through 8 (of 8 total)

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