Problem importing Excel spreadsheet into SSIS

Question

question

Dick Rosenberg asked Feb 14, '10

Problem importing Excel spreadsheet into SSIS

I have an Excel spreadsheet that comes from an outside vendor. It has 16 boilerplate rows and then the data. I need to get rid of the 16 boilerplate rows. I approached this by bringing the entire spreadsheet into an SQL staging tablr and then deleting the top 16 rows.

That worked except all of the numeric columns in the spreadsheet are null. For instance, the SSN of 123456789 which comes in the cell after STUDENT, JOHNNY. I think it is because the boilerplate rows are text but I am not sure.

Any help?

Thanks,

Dick

                               American Student Assistance® (ASA)                                                      
                                Federal Family Education Loan Program                                                   
                                EFT Disbursement Roster Report                                                  
ATTN:  FRANK Administrator                                                                                      Production Date: 1/4/2010
Any COLLEGE                                                                                     School OE Code : 02182900
123 Main Street                                                                                         Report Date: 01/05/2010
Anytown USA                                                                                     Roster Number : ROS000140746
                                Section II: Reissued Disbursements                                                      


                        Loan Type       # of Disbursements or Cancellations     Net Disbursement Total                                          
                        SF      4       $15,132.55                                              
                        SU      1       $831.34                                                 
                        Totals  5       $15,963.89                                              


Borrower Name   Borrower SSN    Student Name    Student SSN     Loan Type       Disb # / Total Disb     Loan Period (Start-End) Loan ID Disbursement Date       Gross Disb Amount       Net Disb Amount Lender Name     Lender ID
STUDENT,JOHNNY  123456789       STUDENT,JOHNNY  123456789       SF       1 /  1 09/21/2009 - 01/09/2010 LOA004886946    LATE    8500.00 8372.50 JPMorgan Chase Bank, N.A. (serviced by Sallie Mae)      807807
STUDENT,JOHNNY  123456789       STUDENT,JOHNNY  123456789       SF       1 /  2 09/21/2009 - 05/15/2010 LOA004877301    1/6/2010        4058.00 3997.13 Chase, JPMorgan Chase Bank N.A. (serviced by Chase      803000
STUDENT,JOHNNY  123456789       STUDENT,JOHNNY  123456789       SF       1 /  3 09/21/2009 - 08/09/2010 LOA004667944    1/6/2010        1500.00 1477.50 Citibank Student Loan Corporation       826878
STUDENT,JOHNNY  123456789       STUDENT,JOHNNY  123456789       SF       1 /  1 09/21/2009 - 01/09/2010 LOA004931422    1/6/2010        1305.00 1285.42 Citibank Student Loan Corporation       826878
STUDENT,JOHNNY  123456789       STUDENT,JOHNNY  123456789       SU       1 /  3 09/21/2009 - 08/09/2010 LOA004667945    1/6/2010        844.00  831.34  Citibank Student Loan Corporation       826878
excel

ssis excel

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-15T06:29:38Z

Simona answered Feb 15, '10

You can try moving your header (Loan Type # of Disbursements or Cancellations..) so as to be the first line in the Sheet.

Asuming your boilerplate rows have a single column, in SSIS, the query might look like:

SELECT [Loan Type] ,[# of Disbursements or Cancellations] FROM [SheetName$]
WHERE [Loan Type] is not null and [# of Disbursements or Cancellations] is not null

Solution 2:

Input File might change

If this 16 no. of rows might change in time you can follow last post at http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/20944506-5276-41e7-8b65-7f36d6e4bdaa)

To sum up, you can define 2 ranges in the Excel file: one for the additional rows(e.g. Summary) and one for the rows you are interested in (e.g.Detail)

This way, in SSIS - EXCEL SOURCE - SQL command type: SELECT [Column1],[Column2] FROM [SheetName$RangeName] WHERE [Column1] is not null

Input file fixed

If the format of the file stay fixed, just hard-code the range in the SQL command: e.g. SELECT * FROM [SheetName$A17:L70]

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-02-14T16:30:00Z

Fatherjack answered Feb 14, '10

Not sure on the SSIS path at the moment but just for you to get the data into SQL Server try scripting it in with BCP or LogParser. These methods can accept a parameter that specifies the first row of data and should get you past your 16 rows. You may be getting the NULL values because of data type issues.

Just as I have been typing this I have thought - can you create a named range in the spreadsheet and reference that from SSIS???

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-02-15T06:57:44Z

Ian Roke answered Feb 15, '10

Would it be possible to save the Excel document as a CSV file and then import it using a Flat File Connection which allows you to skip a certain number of rows from the top of your data.

Check out this URL for the possible options and below for a screen shot of the options.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-02-15T14:14:42Z

Dick Rosenberg answered Feb 15, '10

This was the thing that did it:

If the format of the file stay fixed, just hard-code the range in the SQL command: e.g. SELECT * FROM [SheetName$A17:L70]

I didn't know you could run an SQL statement to filter the rows you were bringing in from an Excel spreadsheet.

Thanks,

Dick

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 5 · 2013-08-05T10:04:06Z

ReksWison answered Aug 5, '13

You may easy make it with how to fix damaged xlsx files [ http://www.fixdamagedxlsxfile.fixxlsxfile.com][1]/ [1]: http://www.fixdamagedxlsxfile.fixxlsxfile.com

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question