Import from non-delimited ASCII file

Posted on 2003-02-26
Medium Priority
Last Modified: 2007-12-19
0) We have DB2 7.2 EE server on win NT 4 & 2000 OS.

1) We have a table having 10 not null columns. There are no primary keys, foreign keys, check constraints & default values.

2) We have a non-delimited ASCII text file having 4 rows and 10 column values. This we were successful in importing to our table.

3) Wehave another non-delimited ASCII text file where there are 4 rows but values for 5th and 10th column are missing. Can we import this data into our table? We did not succeed.

4) If yes, then how? Please give detailed steps of import.

5) Is it true that load utility is same as import utility. The difference is we use load for loading huge data into tables.
Question by:k_murli_krishna
  • 5
  • 4

Expert Comment

ID: 8026108
As far as the parsing of data is concerned the
behaviour of load and import is identical.
The only difference is that for load you can switch
off some of the syntax checking during parsing by
using the 'fastparse' option.

Here's an example:
create table junk ( a varchar(3),
                    b varchar(3),
                    c varchar(3));

A data file containing only data for column 'a' and
'c' (let's assume the file is called junk.dat):

The import command to fill only colums 'a' and 'c' from
import from junk.dat of asc
method L (1 3, 4 6)
replace into junk (a, c);

Expert Comment

ID: 8026132
Oops, forget my comment -- I didn't notice the
'not null'

Expert Comment

ID: 8026222
If you do not have any default values, you won't
be able to load/import that data where some of
the columns are missing.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

LVL 17

Author Comment

ID: 8026555
mglxxx, I will be working further on this tommorow & if no further doubts, I will accept the given comment as answer.
Please go through my question titled 'DB2 7.2/8 EE vs EEE'
LVL 17

Author Comment

ID: 8031348
mglxxx, I am sorry. In the first place, if all columns are not null, then there should be some values. Eveb if they are char(1), i suppose ' ' can be inserted and not '' which is treated as a null.

When we export data from table containing columns having ' ', then will there be any data under that column in the non-delimited/delimited ASCII text like file generated by export from source database. If no, then how do we do the import.

Are there any other methods apart from export & import/load. In third party tools or MS SQL Server or transferring to excel worksheet/MS Access before importing to target DB2 database. You did not keep a comment for my point number 5).

Accepted Solution

mglxxx earned 400 total points
ID: 8031601
I did comment on your item 5 right at the beginning of
my first comment.
There's no way for load/import of an ASC file which lets
you import char/varchar columns which contain an
empty string.
If the file format is DEL and the column delimiter is
',', a NULL column will look like ',,', whereas a column
containing an empty string will look like ',"",'.

If you are using import and do not or cannot export
the data from the source database in DEL format,
you could define a BEFORE trigger which sets the
columns where 'no data' in the import file actually means
'empty string' to an empty string.

Alternatively you could write a small perl script which
converts the ASC file into a DEL file. (I could help
you with that)
LVL 17

Author Comment

ID: 8040777
mglxxx, thank you. i am giving it to you. Can you mail me the perl script to convert ASC into DEL with appropriate instructions. My mail id is k_murli_krishna@hotmail.com. If you can mail me the method of exporting a table data to non-delimited ascii text based file since this feature is not there in export utility, I will be all the more grateful.
LVL 17

Author Comment

ID: 8040782
We did manage to create a formetted single space in text pad and this file whether delimited or non-delimited we successfully imported into our table.

Expert Comment

ID: 8041317
For a quick hack, I'd need the format of the
data file and the data types of the columns to
be loaded from that file. A script which is specific
to that problem is quickly done, a generic script
will take considerably more time and effort.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question