Import CSV From Web As UTF8 And Format Column As Text
Aug 7, 2012
I have a piece of code that imports data from a database on the web. It works but the import uses the wrong encoding. Excel also messes up a column with id-numbers, starting with three zeros that are removed by Excel.
How can a change my code so that it imports the data as utf8 and the columns as text?
Code:
Sub csvAll_diva_update()
Dim url As String
url = "URL;http://kth.diva-portal.org/dice/csvAll?query=-publicationTypeCode:studentThesis&start=0&rows=20&sort=author_sort%20asc"
I am trying to print from excel a text file of a sheet data, creating the text file works but I need to have it encoded as UTF8?? Here is the code I have to print.
[Code] .....
Not sure if I need to add something to this or do I have to open the text file in Notepad and do the convert to UTF8 there?
I have a large text file that is generated daily and want to import into MS Access as the end result.
First I need to reformat into the row format in excel rather than the format it is in. The issue is not all the segments are the same number of lines or they may have mutli message lines. The names with colon : after them I want to be field names in the table which I wish the text file to import into. Please, I am looking for assistance with this.
I have attached sample data of the text dump, and the name of the file is as you see but different date on the end. At the very end of the text file you will see the desired output which I will then import into Access
Also every new message begins with the dotted line and the date and time at the end.
rearrange this macro for me that I could import multiple txt (200 files) files from selected folder. Could You insert appropriate comments in Your altered macro - where I should change how many columns I need to import from files,
- which column/columns I want to import from files
- macro should automatically import data from the whole of the selected column or columns of each text file into one single spread sheet and import it to different columns
I have found this:
Sub test() Dim myDir As String, fn As String, ff As Integer, txt As String Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer myDir = "c: est" '
I am trying to get a VBA code to import multiple text files into an excel worksheet. The text files contain about 5,000 words each listed in a single column. I found this thread Import Multiple Text Files and the code listed there:
Sub test() Dim myDir As String, fn As String, ff As Integer, txt As String Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer myDir = "c: est" '<- change to actual folder path delim = vbTab '<- delimiter (assuming Tab delimited) fn = Dir(myDir & "*.txt") Do While fn <> "" Redim b(1 To Rows.Count, 1 To 1) ff = FreeFile Open myDir & "" & fn For Input As #ff Do While Not EOF(ff)..........................
I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.
However, I have a problem in that my import macro gives me 'Run-time error '1004:
Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!
The code below shows is for the import macro only:
Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer
I have a simple sheet that will require some names in a column. Whenever I try to add a name I get an error message saying " it must be a date greater than or equal to 1/2/1904". Obviously I have formatted this column incorrectly, I have tried to highlight the column, right click Format Cells, text but no luck.
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
I tried to upload my spreadsheet but the zipped excel spreadsheet was 78KB over the forum max. Its hard for me to explan this without you looking at the spreadsheet. Basicly I'm exporting data from Access to Excel into a spreadsheet. When the data exports into excel I want the data to be formated in such a way and this is where some code will need to be written. I've made up 2 spreadsheets within excel. One sheet is how the data comes into excel from access and the other sheet is how I'd like the data to look without me having to doctor the sheet every time I export.
I have been trying to format the rows on this sheet to color scale red based on the number of repeat text strings in Column E. Referring to the attached example sheet, '321/312.2/321.3' appears the most times and the goal is to color code the rows it appears in the deepest shade of red, then the next highest occurring string would shade the rows it appears in a lighter shade, etc, in descending order. Our team currently does this across multiple sheets manually everyday and it would be a real time saver if we could get excel to do this automatically.
I am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook
Sub Format() Dim VarFileName As String Dim VarPath As String Dim VarSavein As String Dim wsheet As Worksheet VarSavein = Sheets("sheet1").Range("C2").Value VarFileName = Sheets("sheet1").Range("A2").Value VarPath = Sheets("sheet1").Range("B2").Value Workbooks.Open VarPath & VarFileName For Each wsheet In ActiveWorkbook.Worksheets Sheets(wsheet.Name).Select Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ := Array(1, 2), TrailingMinusNumbers:=True..........................................
I was wondering if its possible to build a module that would do the following: a) import a txt file to excel formatting it as text.
b) then in column D would remove the preceding space.
c) then convert data in D according to a separately kept Conversion table (or conversion table could be integrated into the code) and print conversion results into column J.
d) the last step is to print/copy columns A and J so that it looks like the final table in Sheet2. Here are files attached.
I get into problem when importing using default CSV settings as numbers and date are understood like text. I need to replace "." with "," to get to number format. Used following Range("J5:K9999").Replace What:=".", Replacement:=",", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False
However I get values multiplied by 100 for some reason.
Converting date format gets eeven more complicated. So I decided I can not use standard CSV import and need to define separate data source for each raw file This is not what I wanted, since I do import many files (10 at this point in time, but it will get growing). However, I would be ready to do that if there is no other option. So I go to "Create New Data Source" and under "Connect" I get to another input box called "ODBC Text Setup" where I select "Options" to define customer format. However, after selecting source file, Excel is not able to interprate fields. Tried clicking "Guess" button, no luck there. Thought it is because fo those "" before and after field value. So, just for a sake of experiment I opened file in text editor and removed those ". Still, can not read the file. The error message says "Text file specification field separator matches decimal separator or text delimiter. Guess failed"
I am out of ideas how to get this file properly imported. Hope there is a way to do that either by defining field formats while importing (perhaps that can be done via SQL, but I am not confident with it) or writing some code to change format after importing from text to Number and Date...
I am wanting to use Excel to perform a web query into an online database our company uses. One of the columns I am wanting to import contains numbers that are 20 digits long & they begin with 4 zero's. The problem is that when Excel imports the query, if I fprmat the cells & preserve the formatting, it cuts off the first 4 zero's & replaces the last number with a zero. Basically, it looks as if it is only importing 15 numbers. Is there a way around this ; is there a way to force Excel to import all 20 numbers? If I don't format the cells, it imports in the following format - 4.20017E+15
The task given was to import selected info from complicated text file and record into excel.
Here is the simple way of showing the txt format (consist of 2 records for example): ================================================= Proxy Id 123 MO ABC ================================================= GID_Group TAX8200 Account 0 (INTRA) loadSharingCandidate 0 (FALSE) RelationType 0 (TRUE) Offset 0 Priority 2
How can i have the output as per attachement? I am not good in importing text files. Shouldi use something call ADO? or what is the most easiest way for me to start?
I was able to import some text from a PDF to excel. My dilemma is that the text is now one long row with CODE in column A then its description in Column B...the next entry has a Code in column C with its description in column D...etc...down the line.
I need Column 1 to be all the codes with description in column B thus allowing Vlookups.
Here is sample:
A B C D E F G H I J AC Araucanian AD Adangme AE Afroasiatic AF Afrikaans AH Amharic
I have about 120 .txt files with Columns A Code of Users[/b], Columns B Number of Conto and Columns C-K with stings to be imported, like Numbers into one .xls file . (see part of examples down and Attachment Red marked)
ABCDEFGHJK.........
Files have names V1.xls, v2.xls ...V120.xls. It have been requested to imported only certain rows e.g. Rows:
02178;5171;untill 02178;5175; Columns C-K, including Columns A i B &Rows only 02178;5179; same Columns C-K, including Columns A i B
Each Cells from C-K should be importing to same Sheets .xls files. Note: String to be converting to Number After importing datas from one files should have take new datas from anothe files, same rows and columns, one under another and Calculating Sums.
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.
I use text to columns everyday at work. Each report that I insert is in the exact same formatting and spacing. So, I open the text file, and manually enter line breaks each time. Is there a way to import text into an already broken up table? Or a way to open a file and it recognizes where to break up the lines without me having to manually click them in each time?
I have a lot of txt. files you would like to imported automatically via VBA code text file is always the same need only certain data in these two rows which is the red just want this data
it is copied from txt. files in Excel so that I had with the click of a button you obtain all the information and through this button you looking for the folder in which the data next would have 2 lines one would be the computer name of one folder and the user can more This would also be the folder in which the data were computer name to the main folder subfolder, users, and wanted to let me read the particulars of which are in the name of the computer and the user. for example. computer name of the user
I recorded the following macro but it only imports files with a specific name. Can someone change the code to allow it open the specific file loaction where I may choose which .txt file I want to import.
I have imported data from textfile reports generated from a dBase system with the following Sub ImportData() ......... End Sub
The problem I have now is that during the operations on the date collumns (G-K) Excel has converted the date to dd/mm/yyyy format, just as I wanted - but has for instance sometimes interpreted the date 10/06/2005 (10 Jun 2005) correctly and sometimes as 06/10/2005 - with no consistent logic I can discern.
I can't get hold again of the source data (the reports) and would need to somehow correct the dates.
The data is historical booking information of a Tour-operator. Therefore there are some hints that would allow me to check if a date makes sense or not. In each row is a start-and an end date in collumns D & E, which would need to be checked for correctness. The following may giv you an idea what I am babbling about: .....
I need to be able to open or import a csv file and have the data be seen as text, not numbers. I can export programming from a phone system as a csv file, modify it and import it back into the phone system. some fields such as "seconds" are in the format 00. Excel sees it as a number and converts it to 0, which causes an error when I try to import again.
I have tried changing the csv to txt and copying it to a blank worksheet and then recording a macro using text to columns, but each export can be laid out differently, so a fixed array doesn't work. there can be over 100 columns so doing it manually and changing each column to text can be quite tedius.
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
I have imported a tab delimited text file into excel. One of the columns consists of a string of text - a comment section. Within this column, there are periods and for some reason it seems that Excel is treating these periods as row separators. how I can prevent Excel from separating the text into rows?
I am trying to write a Macro that will alow me to choose a text file to important but bypass the Import Text Wizard when doing so. I used the record macro function to get this
