EXCEL to SQL import failed due to 32/64 bits

• halifaxdal

  SSCoach

  Points: 19741

  More actions

  December 8, 2016 at 11:32 am

  #338145

  I am stuck here today:

  I have some excel 2013 files with more than the maximum rows that I cannot convert it to excel 2003/2007 format.

  My current office is 2013 and 32 bits while the SQL server is 64 bits, which causes an error:

  ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

  The working solution I found is to uninstall 32 bits office and reinstall 64 bits office so the source and target are on the same bits, however, it will take a long cycle to get 64 bits installed due to management workflow.

  I tried to export it to txt and it doesn't work because column length (I set it to 255) throws truncation error.

  I believe this is a common problem, can anyone shed me more light on how to work around with it?

  Thank you very much.

  Any clue is appreciated.

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  December 8, 2016 at 3:18 pm

  #1916927

  halifaxdal (12/8/2016)

  ---

  I am stuck here today:

  I have some excel 2013 files with more than the maximum rows that I cannot convert it to excel 2003/2007 format.

  My current office is 2013 and 32 bits while the SQL server is 64 bits, which causes an error:

  ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

  The working solution I found is to uninstall 32 bits office and reinstall 64 bits office so the source and target are on the same bits, however, it will take a long cycle to get 64 bits installed due to management workflow.

  I tried to export it to txt and it doesn't work because column length (I set it to 255) throws truncation error.

  I believe this is a common problem, can anyone shed me more light on how to work around with it?

  Thank you very much.

  Any clue is appreciated.

  Why not just export it to a CSV file first?

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• halifaxdal

  SSCoach

  Points: 19741

  More actions

  December 12, 2016 at 6:53 pm

  #1917497

  sgmunson (12/8/2016)

  ---

  halifaxdal (12/8/2016)

  ---

  I am stuck here today:

  I have some excel 2013 files with more than the maximum rows that I cannot convert it to excel 2003/2007 format.

  My current office is 2013 and 32 bits while the SQL server is 64 bits, which causes an error:

  ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

  The working solution I found is to uninstall 32 bits office and reinstall 64 bits office so the source and target are on the same bits, however, it will take a long cycle to get 64 bits installed due to management workflow.

  I tried to export it to txt and it doesn't work because column length (I set it to 255) throws truncation error.

  I believe this is a common problem, can anyone shed me more light on how to work around with it?

  Thank you very much.

  Any clue is appreciated.

  Why not just export it to a CSV file first?

  Thanks. As mentioned in my OP, after the wizard failed with staging it with txt I decided there is probably no need to approach csv way.

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  December 13, 2016 at 7:12 am

  #1917590

  halifaxdal (12/12/2016)

  ---

  sgmunson (12/8/2016)

  ---

  halifaxdal (12/8/2016)

  ---

  I am stuck here today:

  I have some excel 2013 files with more than the maximum rows that I cannot convert it to excel 2003/2007 format.

  My current office is 2013 and 32 bits while the SQL server is 64 bits, which causes an error:

  ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

  The working solution I found is to uninstall 32 bits office and reinstall 64 bits office so the source and target are on the same bits, however, it will take a long cycle to get 64 bits installed due to management workflow.

  I tried to export it to txt and it doesn't work because column length (I set it to 255) throws truncation error.

  I believe this is a common problem, can anyone shed me more light on how to work around with it?

  Thank you very much.

  Any clue is appreciated.

  Why not just export it to a CSV file first?

  Thanks. As mentioned in my OP, after the wizard failed with staging it with txt I decided there is probably no need to approach csv way.

  Then "UNDO" whatever you did to set the length to 255. Unless you really only want the first 255 characters, and are willing to discard the rest... In that case I'd suggest creating a quick macro that you can write using VBA to edit each cell to contain only the leftmost 255 characters. Then export it to CSV. While I don't have Excel 2013, I tried just creating 26 rows of data with 265 characters in each cell in the first column, and the length of each text value is in the second column. I was able to "save" the file as a CSV file, although I did get prompted that I might lose features, but I just said yes and let it go at that. As my column 1 data contained commas, Excel automatically quoted the string. I'd have a hard time believing that this ability disappeared in the 2013 version. However, as Excel determines the data type for each column based on the first 8 rows, you might have a situation where the first 8 rows do NOT have any strings that long, and thus when such data is finally encountered, that becomes the problem. If you can, move at least 1 record with a string value longer than 255 characters to be located within the first 8 rows of data.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)