Last post Sep 16, 2012 11:18 PM by andhaman
Sep 13, 2012 12:19 AM|andhaman|LINK
I have a text file which have around more than 50 rows and 12 columns. Space is the delimiter between each field values. I have to parse this text file and update the values in more than one tables in database based on the value of the first field of the
file. Problem is every row wont have values for all columns. So if we parse based on space delimited it wont work. So could any one of you here please help me out in solving this. Hint is there is specific character width assigned for all the columns in the
file. Reply would be really appreciable.
Sep 13, 2012 09:57 AM|Ajay2707|LINK
I am not confirm but with space it will not possible. because how to define that this is the value of first column , then second column.
for example : if there is 10 space, so the next non-space value is for column 11 or for just next column?
Also your hint is not enough to give data seperation. it is good, if specific width not apply then oracle gives error.
you must clarify with client to give proper delimeter may be ',' or '|' or anything else which is unique and not comes in data part.
Sep 13, 2012 01:56 PM|Lannie|LINK
Fixed length columns.
50 rows and 12 cols so very small.
rename file to *.txt
Open in Excel and use their text import wizard, it supports dropping lines to delimit fixed length cols.
the when the data is imported, check for COMMA symbols.
If none are present you can export as Comma Separated Value file.
Now it is back in TEXT.
then use SQLLOADER, Oracle External Table Loader, SQL Developer, or TOAD to import the comma delimted file.
empty cells will be treated as NULLs.
then you can use SQL scripts to import what you need to do to get the data loaded into tables.
Sep 16, 2012 09:22 PM|andhaman|LINK
Thanks Ajay for the reply. I cudnt understand what you meant in the 2nd and 3rd line. But the file is a kind of fixed format which deals with multiple companies, and client are not ready to give a proper delimiter. I will try to explain bit more clear for
example: a table is having three fields Name (30), Sex(2), DOB(8), datas which will be coming the file will be like:
Sam M 21/07/1988
Since Name is given 30 character width, M in the first row will come in the 31st space only. At the same time if we use space as a delimiter then in the 3rd row 22/03/1984 will be stored under sex column. Hope it makes more clear. So was and still wondering
whether is it possible to parse the text file based on fixed width. Any replies with solution would be really appreaciable. Thank you.
Sep 16, 2012 09:24 PM|andhaman|LINK
Thanks Lannie for the reply. I am not allowed (authorized) to convert that text file to any other formats. All I have to do it operated with that file. Could you please let me know whether is it possible to parse the text file based on fixed width. Thank
Sep 16, 2012 10:54 PM|Lannie|LINK
ORACLE SQLLOADER and EXTERNAL TABLE loader both allow fixed length column uploads.
External table has one additional requirement, you need an Oracle Directory Object to hold the flat file while you access it.
INTO TABLE myTable
col1 POSITION(01:04) CHAR,
col2 POSITION(06:15) CHAR,
col3 POSITION(17:25) CHAR,
col4 POSITION(27:30) CHAR,
col5 POSITION(32:39) CHAR,
col6 POSITION(41:48) CHAR,
col7 POSITION(50:51) CHAR
Sep 16, 2012 11:18 PM|andhaman|LINK
Thanks very much Lannie, it seems to be almost a solution. Could you please clarify a couple of queries which I have regarding this:
1). Actually the data which I need to insert is into 4 tables based on some conditions. So is it possible to write conditions in where clause using QUERY in the above example.
2). I use Oracle SQL Developer as a tool to run the queries, where ORACLE SQLLOADER is a separate tool to be downloaded.
3). Whether the above code can be used with .NET to provide the user UI interface.
Reply would be really appreciable. Thanks very much for your time.