Rectangle 27 4

Pulling in Date or other mixed-data column items in the past has bit me with Excel.

It seems that their provider "peeks" ahead XX rows (depending on Provider version) to determine what the column type is at runtime. The prior answers that apply properties to your connection have helped me in the past, but do not help when you have BLANK dates and/or different values in the column itself - it confuses the Excel driver.

What I recommend is that you use FileHelpers or another third-party library instead. Infragistics Excel component has treated me very well, while FileHelpers is opensource.

c# - DateTime format mismatch on importing from Excel Sheet - Stack Ov...

c#
Rectangle 27 26

I had this issue when importing from a flat, delimited file into SQL Server. The solution was to update the 'OutputColumnWidth' value for the offending column (from the error message). On the 'Choose a Data Source' form in the import wizard, my source was the flat file. On the leftmost pane, choose 'Advanced'. You can then set the properties of individual columns. In my case, the 'OutputColumnWidth' for most of my columns was defaulted to '50'. I simply updated it to a larger value that would not truncate the value from the flat file.

Alternatively, rather than guessing a large enough limit to a DT_STR, you can choose the DT_NTEXT SSIS type, which is the equivalent of the MSSQL nvarchar(max) or the obsolete ntext types.

A combination of updating the OutputColumnWidth and using DataType DT_WSTR worked for me. In SQL Server 2014, DT_WSTR can have an OutputColumnWidth up to 4,000 Unicode characters wide. This resulted in something similar to 40-Love's answer below.

Can you change all of these columns at once? I have a high amount of columns and I was wondering if this was possible.

sql server 2008 - Text was truncated or one or more characters had no ...

sql-server-2008 excel ssis codepages import-from-excel
Rectangle 27 2

When linking Excel spreadsheets in MS Access, a problem arises when a column has mixed data types. For example, if the first row in the column is "Text" and the remaining are Numeric, the Numeric fields will be formatted as Text and #Error out. Likewise with Dates and non-Dates mixed in the same column.

There are some nice attempts at answers up there, but here is the simple solution to read these Mixed data types without a data type mismatch error:

This solved the problem for me. Basically my code handles the data input based on culture info. So all I needed was the data from the excel to be in string format not DateTime format. Adding "IMEX=1" to the connections string did the trick.

c# - DateTime format mismatch on importing from Excel Sheet - Stack Ov...

c#
Rectangle 27 1

You could use the British Culture info to parse the date in British Format dd/MM/YYYY

CultureInfo culture = new CultureInfo("en-GB");
DateTime date = DateTime.Parse("27/12/2009", culture);

This doesn't work as the OleDbDataReader automagically converts the text into a date before you can even get the raw value.

c# - DateTime format mismatch on importing from Excel Sheet - Stack Ov...

c#
Rectangle 27 12

Recently I had similar problem, I solved it by importing a HTML file, the baseline example would be like this:

<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
  <head>
    <style>
      <!--
      br {mso-data-placement:same-cell;}
      -->
    </style>
  </head>
  <body>
    <table>
      <tr>
        <td>first line<br/>second line</td>
        <td style="white-space:normal">first line<br/>second line</td>
      </tr>
    </table>
  </body>
</html>

I know, it is not a CSV, and might work differently for various versions of Excel, but I think it is worth a try.

downvote, not a CSV.

@GusDeCooL The wording of OP's first sentence "I need to generate a file for Excel, some of the values in this file contain multiple lines." suggests that perhaps it does not need to be a CSV file. Besides, the Q&A format applies to other readers as well, and it might be a viable choice for some of them (even if the OP had to use CSV). I find your downvote unreasonable (still, thank you for explaining why).

Its a good idea.

Generating CSV file for Excel, how to have a newline inside a value - ...

excel csv newline
Rectangle 27 12

Recently I had similar problem, I solved it by importing a HTML file, the baseline example would be like this:

<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
  <head>
    <style>
      <!--
      br {mso-data-placement:same-cell;}
      -->
    </style>
  </head>
  <body>
    <table>
      <tr>
        <td>first line<br/>second line</td>
        <td style="white-space:normal">first line<br/>second line</td>
      </tr>
    </table>
  </body>
</html>

I know, it is not a CSV, and might work differently for various versions of Excel, but I think it is worth a try.

downvote, not a CSV.

@GusDeCooL The wording of OP's first sentence "I need to generate a file for Excel, some of the values in this file contain multiple lines." suggests that perhaps it does not need to be a CSV file. Besides, the Q&A format applies to other readers as well, and it might be a viable choice for some of them (even if the OP had to use CSV). I find your downvote unreasonable (still, thank you for explaining why).

Its a good idea.

Generating CSV file for Excel, how to have a newline inside a value - ...

excel csv newline
Rectangle 27 12

Recently I had similar problem, I solved it by importing a HTML file, the baseline example would be like this:

<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
  <head>
    <style>
      <!--
      br {mso-data-placement:same-cell;}
      -->
    </style>
  </head>
  <body>
    <table>
      <tr>
        <td>first line<br/>second line</td>
        <td style="white-space:normal">first line<br/>second line</td>
      </tr>
    </table>
  </body>
</html>

I know, it is not a CSV, and might work differently for various versions of Excel, but I think it is worth a try.

downvote, not a CSV.

@GusDeCooL The wording of OP's first sentence "I need to generate a file for Excel, some of the values in this file contain multiple lines." suggests that perhaps it does not need to be a CSV file. Besides, the Q&A format applies to other readers as well, and it might be a viable choice for some of them (even if the OP had to use CSV). I find your downvote unreasonable (still, thank you for explaining why).

Its a good idea.

Generating CSV file for Excel, how to have a newline inside a value - ...

excel csv newline
Rectangle 27 2

This may be caused by Excel Jet driver default settings. Check out the following registry key and change it's value from default 8 to 0, meaning "guess column data type based on all values, not just first 8 rows."

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
"TypeGuessRows"=dword:00000000

Well, it does work in that Access is now guessing the datatype more intelligently. However, I still do not have the power to change the datatype to whatever I please during the import process. I do realize that I may have no other option, though. If that is the case, I'll accept this as the answer.

Change datatype when importing Excel file into Access - Stack Overflow

excel ms-access import
Rectangle 27 3

There are a couple of ways to do this. The most straightforward way is to convert the .xls file to a .csv file in Excel, so you can import into Access using the Import Text Wizard, which allows you to choose the data types of every column during the import.

The other benefit to doing this is that the import of a csv (or text) file is so much faster than the import of an xls file. If you're going to do this import more than once, save the import setup settings as an import specification. (When in the Import Text Wizard, click on the "Advanced..." button on the bottom left, then click on "Save As" and give a specification name to save the changes you just made.)

Change datatype when importing Excel file into Access - Stack Overflow

excel ms-access import
Rectangle 27 1

There is an alternative location of the registry component that needs to be changed to resolve this problem.

then look in

sql server 2008 - Text was truncated or one or more characters had no ...

sql-server-2008 excel ssis codepages import-from-excel
Rectangle 27 1

  • Copy the row which has cell value > 255 characters to the beginning of the excel, make that row the first row in the excel
  • change the registry value from the above link.

sql server 2008 - Text was truncated or one or more characters had no ...

sql-server-2008 excel ssis codepages import-from-excel
Rectangle 27 3

I think you best use the import facility in excel but you may have to manually change the file extension to a csv. When importing be sure to select text for all the columns with these values. share|improve this answer edited Apr 10 '13 at 11:29 answered Apr 10 '13 at 11:18 glh 4,39821538

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

Excel wrongly converts ranges into dates, how to avoid it? - Stack Ove...

excel date implicit-conversion tsv
Rectangle 27 1

Most likely you are not importing the file but rather you are opening the file. If you import the file the text import wizard will open, and you will be able to define the comma as the delimiter.

In early versions of Excel, this is a drop down next to the file open button. In recent versions of Excel, you select the Get External Data tab on the Data Ribbon, and select the From Text button.

matlab - How to prevent Excel from importing CSV values as single larg...

excel matlab csv
Rectangle 27 1

I've changed the data type of Mobile_Numbers to bigint rather than only int in mysql. And its working properly. Thanks a lot Adyson for great help.

How to ignore exponential notification and get the actual value while ...

mysql excel csv load-data-infile
Rectangle 27 1

I do this for credit card numbers which keep converting to scientific notation: I end up importing my .csv into Google Sheets. The import options now allow to disable automatic formatting of numeric values. I set any sensitive columns to Plain Text and download as xlsx.

If you bother to read through some of the other answers, you will see that someone already suggested this here.

I did bother, thank you. I hint at the automatic formatting selection being a new part of the import process. My answer provides a very specific workflow, for those who, like me, have been forced to use Sheets to deal with this specific problem. In my mind, this merited a separate answer. A minor adjustment of your tone might actually result in your comment being interpreted as helpful, which is the whole point of this family of websites.

csv - Stop Excel from automatically converting certain text values to ...

excel csv import
Rectangle 27 1

I do this for credit card numbers which keep converting to scientific notation: I end up importing my .csv into Google Sheets. The import options now allow to disable automatic formatting of numeric values. I set any sensitive columns to Plain Text and download as xlsx.

If you bother to read through some of the other answers, you will see that someone already suggested this here.

I did bother, thank you. I hint at the automatic formatting selection being a new part of the import process. My answer provides a very specific workflow, for those who, like me, have been forced to use Sheets to deal with this specific problem. In my mind, this merited a separate answer. A minor adjustment of your tone might actually result in your comment being interpreted as helpful, which is the whole point of this family of websites.

csv - Stop Excel from automatically converting certain text values to ...

excel csv import
Rectangle 27 1

I do this for credit card numbers which keep converting to scientific notation: I end up importing my .csv into Google Sheets. The import options now allow to disable automatic formatting of numeric values. I set any sensitive columns to Plain Text and download as xlsx.

If you bother to read through some of the other answers, you will see that someone already suggested this here.

I did bother, thank you. I hint at the automatic formatting selection being a new part of the import process. My answer provides a very specific workflow, for those who, like me, have been forced to use Sheets to deal with this specific problem. In my mind, this merited a separate answer. A minor adjustment of your tone might actually result in your comment being interpreted as helpful, which is the whole point of this family of websites.

csv - Stop Excel from automatically converting certain text values to ...

excel csv import
Rectangle 27 1

I do this for credit card numbers which keep converting to scientific notation: I end up importing my .csv into Google Sheets. The import options now allow to disable automatic formatting of numeric values. I set any sensitive columns to Plain Text and download as xlsx.

If you bother to read through some of the other answers, you will see that someone already suggested this here.

I did bother, thank you. I hint at the automatic formatting selection being a new part of the import process. My answer provides a very specific workflow, for those who, like me, have been forced to use Sheets to deal with this specific problem. In my mind, this merited a separate answer. A minor adjustment of your tone might actually result in your comment being interpreted as helpful, which is the whole point of this family of websites.

csv - Stop Excel from automatically converting certain text values to ...

excel csv import
Rectangle 27 2

Its not the Excel. Windows does recognize the formula, the data as a date and autocorrects. You have to change the Windows settings.

"Control Panel" (-> "Switch to Classic View") -> "Regional and Language Options" -> tab "Regional Options" -> "Customize..." -> tab "Numbers" -> And then change the symbols according to what you want.

It will work on your computer, if these settings are not changed for example on your customers' computer they will see dates instead of data.

csv - Stop Excel from automatically converting certain text values to ...

excel csv import