DTS: File to Access (fixed field and no row delimiter)

Please not this article is part of a larger article

Sometimes you may come across a text file that stores all of its information in a column rather than have the fields you want to import in one row and delimited by a recognised marker. Instead you have something like this:

Field 1
Field 2
Field 3
….

This makes importing a text file slightly trickier as you have to convert each row into a column. Thankfully DTS allows you to do this quite simply (once you know how). First off select your data source type, i.e. in this case Text File, from the drop down menu.Next select the file you wish to import by clicking on on the Browse button (…) next to the field labelled File Name. Once you are happy with your selection, click Next.

First thing to do when setting the file format is to choose Fixed field from the first two options. The file type should be set to ANSI, the row delimiter to <none> and the text qualifier should be greyed out. OK, click Next. You can now see that you have transformed the rows into columns. First you have to define the Row width. This is done by moving the red line to the end of the line of text pictured. You may also notice that the row ends with ||, make sure you set your red line after those two ||. Click Next again. In this dialogue you specify the the fixed width of your columns. Again you will note the ||, you place your column delimiter after those || by clicking with your mouse just behind the last |. You do this for every single field. Take great care when positioning your delimiter, any mistake will result in your data no being exported. On the upside troubleshooting the misplaced column break is easy as it only imports that is properly marked off. Hence any missing data should indicate a misplaced column break.

Once you are done click Next. Now you choose your destination source. From the Destination drop down menu, choose Microsoft Access. You will now have to specify your destination file. Once you have done that and set any, if necessary, security options click Next. Now you are in a position to preview the fruits of your labour. If everything went according to plan, by highlighting the destination table and clicking on the Preview button, you will fire up a dialogue that should display your selected data and how it will appear in it’s new table. At this stage I would like to point out that by default the export is to a new table (more information on how to append to an existing table will be published later [i.e. once I figure out how to do that]). If you are happy with the prospective result, click Next again.

Right this concludes this section of the File to Access (fixed field and no row delimiter), please go back to the previous document by clicking here to complete the transformation.