Home » RDBMS Server » Server Utilities » Unable to load CSV file to Oracle Table (SQL Loader, Windows)
Unable to load CSV file to Oracle Table [message #683639] Tue, 09 February 2021 05:24 Go to next message
srai.bi
Messages: 11
Registered: February 2021
Junior Member
Hi,
I am trying to load a CSV file generated by Oracle Hyperion Planning system to Oracle Table using SQL Loader.
When I open the CSV file in excel all data looks perfect!!

The file contains 50 columns out of which 5 columns contains formulas. Example: @VAR("Forecast","Budgets");
Though the formula contains "," it is still appearing properly in excel (.csv) but when I load it to the table I face following issues:
1. Data from formula columns gets loaded to other columns.
2. @VAR("Forecast","Budgets"); gets loaded as @VAR(""Forecast"",""Budgets""); ---> with extra double quote

Below is my control file: I tried using FILLER for formula columns which I don't want in table. I mean, even if formula column data gets loaded its fine or I can skip it also.
OPTIONS (SKIP=1)
LOAD DATA
TRUNCATE
INTO TABLE FRS.PBCS_CLASS_CODE_EXTRACT_STG
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '""'
TRAILING NULLCOLS
( CLASSCODES,
PARENT,
ALIASDEFAULT,
VALIDFORCONSOLIDATIONS,
DATASTORAGE,
TWOPASSCALCULATION,
DESCRIPTION,
FORMULA FILLER,
FORMULADESCRIPTION,
UDA,
SMARTLIST,
DATATYPE,
HIERARCHYTYPE,
ENABLEFORDYNAMICCHILDREN,
NUMOFPOSSIBLEDYNCHILD,
ACCESSGRANTEDTOMEMBERCREATOR,
ALLOWUPPERLEVELENTITYINPUT,
PROCESSMANAGEMENTENABLED,
UUID,
DATAID,
OLDNAME,
OLDUNIQUENAME,
ACCOUNTTYPE,
TIMEBALANCE,
SKIPVALUE,
EXCHANGERATETYPE,
VARIANCEREPORTING,
SOURCEPLANTYPE,
PLANTYPEFINPLAN,
AGGREGATIONFINPLAN,
DATASTORAGEFINPLAN,
FORMULAFINPLAN FILLER,
FORMULADESCFINPLAN,
PLANTYPEFINRPT,
AGGREGATIONFINRPT,
DATASTORAGEFINRPT,
FORMULAFINRPT FILLER,
SOLVEORDERFINRPT,
FORMULADESCFINRPT,
PLANTYPEASR,
AGGREGATIONASR,
DATASTORAGEASR,
FORMULAASR FILLER,
FORMULADESCASR,
PLANTYPEMYB,
AGGREGATIONMYB,
DATASTORAGEMYB,
FORMULAMYB FILLER,
FORMULADESCMYB,
OPERATION
)

CALL sqlldr !ouser!/!opwd!@!odb! CONTROL="..\ControlFiles\CC_SQL.ctl" LOG="..\Logs\CC_SQL_!filename!.log" BAD="..\Logs\CC_SQL_!filename!.bad" DATA="%extractmetadatadir%\!filename!"

Please help. Thanks.

Report message to a moderator

Re: Unable to load CSV file to Oracle Table [message #683640 is a reply to message #683639] Tue, 09 February 2021 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

CSV files are pure text files, Excel interprets some data as formulas but it is just an interpretation of Excel.
Oracle loader does not interpret anything, it just takes text as text.

Report message to a moderator

Re: Unable to load CSV file to Oracle Table [message #683641 is a reply to message #683640] Tue, 09 February 2021 06:18 Go to previous messageGo to next message
srai.bi
Messages: 11
Registered: February 2021
Junior Member
Thanks.

How do I resolve these 2 issues. Pls help.

Thanks
Sid

Report message to a moderator

Re: Unable to load CSV file to Oracle Table [message #683642 is a reply to message #683641] Tue, 09 February 2021 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to export "with values" and not "with formulas".
This is an Hyperion issue not and SQL*Loader one.

Report message to a moderator

Re: Unable to load CSV file to Oracle Table [message #683647 is a reply to message #683642] Tue, 09 February 2021 19:10 Go to previous messageGo to next message
srai.bi
Messages: 11
Registered: February 2021
Junior Member
Thanks. I was able to resolve the issue using powershell Smile

Report message to a moderator

Re: Unable to load CSV file to Oracle Table [message #683649 is a reply to message #683647] Tue, 09 February 2021 23:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you post what you did to help future readers with the same problem.

Report message to a moderator

Re: Unable to load CSV file to Oracle Table [message #683728 is a reply to message #683649] Tue, 16 February 2021 16:37 Go to previous messageGo to next message
srai.bi
Messages: 11
Registered: February 2021
Junior Member
Hi Michel,

I created a ps1 script which removes the unwanted column.

Import-CSV .\Filename.csv | Select-Object * -ExcludeProperty "Unwanted 1","Unwanted 2","Unwanted 3","Unwanted 4","Unwanted 5" | Export-CSV .\OutputFile.csv -NoTypeInformation

Cheers,
Sid

Report message to a moderator

Re: Unable to load CSV file to Oracle Table [message #683731 is a reply to message #683728] Wed, 17 February 2021 00:03 Go to previous message
Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Report message to a moderator

Previous Topic: Optimization of sqlldr performance
Next Topic: impdp bypassing DUMPFILE
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Sep 08 11:44:29 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.