FAERS challenges in uploading ASCII files into an Oracle database.

Downloaded data from FDA FAERS? Now you are having difficulties in uploading ASCII files into an Oracle database via SQL*Loader? Lets list out the issues.

• Problem: DRUGyyQn.txt is too big ( DRUG24Q2.txt 187,899 KB)
• Solution: Split the file into 4 smaller files using following splitfile.bat script

—————————————————–

@echo off
setlocal enabledelayedexpansion

set “file=%1”
set “lines=%2”
set “count=0”
set “part=1”

for /f “tokens=*” %%a in (%file%) do (
set /a count+=1
echo %%a >> part!part!.txt
if !count! equ %lines% (
set /a part+=1
set count=0
)
)

——————————————————-

cmd> splitfile.bat DRUG24Q2.txt 50000

Load split files one at a time. You may get 4 split files. So, you would have loaded data in 4 tables.

Merge all tables created via sql*load to one table. SQL> insert into PART1 select * from PART4