cancel
Showing results for 
Search instead for 
Did you mean: 

Excel data to SQL server Loading

former_member281308
Participant
0 Kudos

Hi ,

I am getting below the error . Can you please explain me how to resolve this error?

|Data flow DF_EDS_ADC_MMAP_CLAIMS_IB|Reader FF_EDS_ADC_MMAP_CLAIMS_IB1

OLE or COM processing error. Please make sure Microsoft Access Database Engine is properly installed: <'Sheet1$' is not a valid

name. Make sure that it does not include invalid characters or punctuation and that it is not too long.>.

(14.2) 09-27-16 12:34:06 (E) (3420:5192) EXL-300873: |Data flow DF_EDS_ADC_MMAP_CLAIMS_IB|Reader FF_EDS_ADC_MMAP_CLAIMS_IB1

OLE or COM processing error. Please make sure Microsoft Access Database Engine is properly installed: <Operation is not allowed

when the object is closed.>.

(14.2) 09-27-16 12:34:07 (E) (4104:5876) RUN-053003: Zero divide error. Divisor is equal to zero.

(14.2) 09-27-16 12:34:10 (E) (4520:3332) EXL-300875: |Data flow DF_EDS_ADC_MMAP_CLAIMS_IB

Could not access Excel file

<//tc-ftp/FTP-Site/test/KKapadia/WR_Conversion/Sprint_8/ADC_MMAP_Claims/Incoming/TEST_OmniSYS_Abbott_Medicare_08_31_2016.xls>

(14.2) 09-27-16 12:34:10 (E) (4520:3332) EXL-300873: |Data flow DF_EDS_ADC_MMAP_CLAIMS_IB

OLE or COM processing error. Please make sure Microsoft Access Database Engine is properly installed: <Could not decrypt file.>.



Thanks,

krish.

Accepted Solutions (0)

Answers (11)

Answers (11)

former_member281308
Participant
0 Kudos

Hi Ravi,

The client has created FTP and I am accessing the txt files through ftp and source is txt files

If i am uisng individually the job is sucessful and if i run multiple file  getting an error.

System Exception <Access_Violation> Occur. Process dump option is off.

Thanks,

krishna.

former_member281308
Participant
0 Kudos

Hi ravi,

Source is Flat file and Target is SQL server

The above the Image is source file and showing the column values

and there is no spaces above the source file. Can you please let us know .

Regards,

krishna.

former_member208402
Active Contributor
0 Kudos

Hi Kris,

If your target table has only 1 column and if it is of size that can accept that text from the flat file then it will not fail...

otherwise it will fail

former_member281308
Participant
0 Kudos

Hi Ravi,

The flat file showing  below the image. the member number is column and remaining numbers are showing column. ifi run this flat file should be getting an error or not.

please let us know..

Thanks,

krish.

former_member208402
Active Contributor
0 Kudos

Hi kris,

what is your target here? and are there any spaces in the file between each column value?

former_member281308
Participant
0 Kudos

Thanks You Ravi

former_member281308
Participant
0 Kudos

Hi Ravi,

I  have one doubt for BODS Side

I can give the Value for Rows Per Commit =1. If we are giving to the  rows per commit 1 , the performance is good.

if you know the answer please explain.

Thanks,

krish.

former_member208402
Active Contributor
0 Kudos

Hi Kris,

if you set rows per commit=1 and  then the performance will be worse as DS sends commit to target for each record.

use rows per commit between 500-3000 based on your amount of data and maximum value of rows per commit is 5000

former_member281308
Participant
0 Kudos

Hi Ravi,

Thanks for replay! I have Used for date column  and I have source data one excel sheet coming the data (20160925;20160926) and another excel file data is coming(2016/09/25;2016/09/26) that s why i have implemented above  the logic , If i ran the job the target showing for date cloumn null values. i think the above code is not work . i am trying analyze other logic..

Thanks,

krish.

former_member208402
Active Contributor
0 Kudos

Hi Krish,

After excel source take a query transform and in the "Term Date" column mapping tab write below logic

to_date("Term Date",'yyyymmdd') 

Now take another query transform next to this and write your infthenelse() on this date column and try executing the job.

former_member281308
Participant
0 Kudos

Hi Ravi,

Can you write the Query   then i need ti understand the logic

Thanks

krishna.

former_member208402
Active Contributor
0 Kudos

Hi Kris,

Tell me this.. where are you using this logic

ifthenelse( is_valid_date( FF_EDS_ADC_MMAP_CLAIMS_IB."Term Date", 'YYYYMMDD') = 1, FF_EDS_ADC_MMAP_CLAIMS_IB."Term Date", null)

former_member281308
Participant
0 Kudos

Hi Ravi, I will implement what you are giving the code.

I have another doubt

I have written the code for date column .

In Excel  we are using the date format mm/dd/yyyy . when i run the job the date column showing null value in target table.

Initial Code:

ifthenelse( is_valid_date( FF_EDS_ADC_MMAP_CLAIMS_IB."Term Date", 'YYYYMMDD') = 1, FF_EDS_ADC_MMAP_CLAIMS_IB."Term Date", null)

After code:

to_date(FF_EDS_ADC_MMAP_CLAIMS_IB."Term Date",'yyyy-mm-dd')

Can you please let us know.

Thanks,

krish.

former_member208402
Active Contributor
0 Kudos

Hi Krisk,

First convert the date format mm/dd/yyyy to yyyymmdd and then apply is_valid_date function in infthenelse.

I suspect that you are validating mm/dd/yyyy date format with YYYYMMDD in is_valid_date function so the validation failsin ifthenesle() function and you are getting null.

former_member281308
Participant
0 Kudos

Hi Ravikiran,

Thanks for replay! I will try for above the logic.

I have 3 excel files

First excel file, the  sheet name is sheet1,

Second excel file, the sheet name is freedom_06302015 and next file will come freedom_07312016 but dsheet dates are different.

third excel file, the sheet name is  omni_06302015 and next file will come omnisys_07312016  but sheet dates are different  but structure is same for all 3 files

If i ran the job the first excel file data is loaded in to target

because the excel file name is sheet1

the 2nd & 3rd excel files is not loaded in the target table

because the sheet names are different.

Thanks,

krish.

former_member281308
Participant
0 Kudos

Hi Dirk,

I have created folder and i put 3 different excel files.

for these excel files are same columns names but different data

1. sheet tab name is sheet1

2. sheet tab name is freedom_0630

3. sheet tab name is omni_0630

how to process these 3 excel files in data services?

former_member208402
Active Contributor
0 Kudos

Hi Kris

While creating excel source format select worksheet and give a global variable ($G_SHEETNAME) instead of real sheet name.

initialize a script and pass ($G_SHEETNAME=sheet1) for that global variable and take a Loop object next.

since you have 3 files take a global variable ($G_Loop=1) and  give $G_Loop<=3 and inside loop take a dataflow with your excel source and take a script after dataflow.

when $G_Loop=1 youe excel sheet (sheet1) will be picked up and after DF executs it will go to script next to DF and in that script you make

$G_Loop=$G_Loop+1;

$G_SHEETNAME=freedom_0630;

now it will pick your 2nd sheet.

This will repeat 3 times and loads your files.

Please let me know if you need more information on this.

Thanks,

Ravi kiran.

former_member187605
Active Contributor
0 Kudos

I assume this is the real error:

Could not access Excel file

//tc-ftp/FTP-Site/test/KKapadia/WR_Conversion/Sprint_8/ADC_MMAP_Claims/Incoming/TEST_OmniSYS_Abbott_Medicare_08_31_2016.xls

Are you sure the file can be read by the DS service user?