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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

741 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