I will pm you a dropbox link with the file later, so you can check it out yourself.

Switch from Fixed with to Delimited with. Choose the Data tab from the Ribbon -> From Text in the Get External Data section -> Select File -> Click Import. Select Delimited, Start Import at row 1 and Windows (ANSI), click Next. Select Data > Get External Data > From Text. I must have imported the file at least 20 times now and its always the same lines that produce the error. The following describes how to use the basic CSV import utility prior to 1704. Save the Excel spreadsheet as Text (Tab Delimited). And guess what: it worked fine this time! I have no idea how this is possible, I guess it must have something to do with the filesize?īut the problematic lines all had one thing in common, there was always some kind of non-letter sign or special letter in the item description, directly before the semicolon: various punctuation marks, signs like 'ø', or french and german special letters like 'é' or 'ä'. So I thought: maybe I changed something that caused the error? To verify I copy/pasted a range of about 100 consecutive rows out of the file into a new one, and this range contained one of the problematic ones.

Step 2: In the next window, select the other and mention comma. Step 4: By selecting the Delimited, click on NEXT. Step 3: It will open up a Text Import Wizard.

Select the targeted file then, Click on Import. Step 2: Now, it will ask you to choose the file location on the computer or laptop. After receiving a new laptop, and installed the exact latest version of Office 365 this option is gone. Step 1: Go to the DATA tab and click on From Text. Until now, I used Paste > Text Import Wizard for that. I copied those examples from a file with about 27000 rows, but I changed the values between the semicolons as those datasets contain data that maybe somewhat confidential. Hi, I am usually copying data via clipboard between different Excel sheets or external programs and need to transform these data (e.g. 'OLEDB Provider= is really strange, I just tried it with only those two and it worked fine as you said. 'Conexión a la consulta 'data' en el libro.', _ Workbooks(This_workbook).Connections.Add2 'Consulta - data', _ Using the Macro Recorder I got a long code that gave me the solutionĪ Name:='data', Formula:= _ Following comment, I'm now using Excel 2016.

Using the text import wizard in excel