Data import from excel file issue

[jrg24]

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.

 

[MD610]

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]

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]

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

 

[jrg24]

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?

 

[NickyvV]

The exact description is here: PRB: Excel Values Returned as NULL Using DAO OpenRecordset
Also note the TypeGuessRows property on this page when IMEX=1 does not solve your problem.