x

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
more ▼

asked Feb 14, 2010 at 01:06 PM in Default

Dick Rosenberg gravatar image

Dick Rosenberg
103 6 6 7

(comments are locked)
10|1200 characters needed characters left

5 answers: sort oldest

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]

more ▼

answered Feb 15, 2010 at 06:29 AM

Simona gravatar image

Simona
47 1 1 2

(comments are locked)
10|1200 characters needed characters left

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???

more ▼

answered Feb 14, 2010 at 04:30 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left

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.

IMAGE1

more ▼

answered Feb 15, 2010 at 06:57 AM

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Feb 15, 2010 at 02:14 PM

Dick Rosenberg gravatar image

Dick Rosenberg
103 6 6 7

(comments are locked)
10|1200 characters needed characters left

You may easy make it with how to fix damaged xlsx files

http://www.fixdamagedxlsxfile.fixxlsxfile.com/

more ▼

answered Aug 05, 2013 at 10:04 AM

ReksWison gravatar image

ReksWison
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x939
x115

asked: Feb 14, 2010 at 01:06 PM

Seen: 8094 times

Last Updated: Aug 05, 2013 at 10:04 AM