Data import from excel file issue

jrg24

New Member
Joined
Aug 6, 2010
Messages
49
I hope you guys can help with my issue. I have a large excel file that I need to use for weekly data analysis. Every week, new data is added and the possibility for old data to change is present (columns stay constant though). I cannot connect directly to the database source as far as I know, so the solution I was trying to implement was to select "Use other sources" and select excel file. My plan was to download the new spreadsheet and save-as the old file's name. This way I could just refresh my data sources and the updated data would appear in powerpivot. I am not sure if this will work (will it?)

I am having an issue with my office code field. I have values that are numbers and some that are text (all are formatted as general). During the import it appears to be deleting the values that are text and leaving the field blank. Does anyone know how I could fix this issue? Thanks for any help you could give.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

MD610

Board Regular
Joined
Feb 7, 2012
Messages
188
As long as the file has the same fields and file name, that should work. The other option might be to copy the data into an excel tab in your PowerPivot workbook and just create a linked table to PowerPivot. A direct connection to data would definitely be easiest and reduce manual effort if you can get the permissions.

As far as the formatting issue, I would try highlighting the entire field and formatting it as text in excel prior to uploading it to PowerPivot. Saving the data as a text file (as opposed to excel) would probably solve this as well. Powerpivot can use a text file as a data source too.
 

jrg24

New Member
Joined
Aug 6, 2010
Messages
49
As long as the file has the same fields and file name, that should work. The other option might be to copy the data into an excel tab in your PowerPivot workbook and just create a linked table to PowerPivot. A direct connection to data would definitely be easiest and reduce manual effort if you can get the permissions.

As far as the formatting issue, I would try highlighting the entire field and formatting it as text in excel prior to uploading it to PowerPivot. Saving the data as a text file (as opposed to excel) would probably solve this as well. Powerpivot can use a text file as a data source too.


sorry for the delayed response. Thanks for your input. I am currently using a linked table but I would prefer to not do this in the future to keep the file size down since this needs to be emailed out. I will try saving the file as text to see if that works. Thanks for your input.
 

NickyvV

New Member
Joined
Feb 8, 2012
Messages
49
Your problem with the formatting can be solved in the following way:
Edit the connection to your Excel-file, on the advanced button edit the top field (Extended Properties) and add ";IMEX=1" (without the dubble quotes). It should have already something like: "Excel 12.0;HDR=Yes". Save the changes and click OK.


It could be that you have to change to datatypes of the columns to TEXT in PowerPivot manually afterwards, because they're set to numbers at the moment. If you refresh now than you could get an error if you don't change them. Or you could try and import the table(s) from scratch.
The reason Excel (or the underlying engine) deletes the text fields, is that it tries to guess the datatypes on the first few rows in the dataset. Whenever there's a value that doesn't comply to this datatype the engine ignores it.

Please let me know if that works for you!
HTH
 
Last edited:

jrg24

New Member
Joined
Aug 6, 2010
Messages
49
Thanks, NickyvV! That fixed my problem. You just saved me a ton of time and a bunch of file size! Just curious, what does the ";IMEX=1" do?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,470
Messages
5,572,307
Members
412,453
Latest member
Parbiana
Top