How To Best Flatten A Tab-delimited Table
Aug 3, 2010
Here's an example data table (the data are fake, but in the same format as my business data coming from an external system):
R1 Pears,Apples,Bananas 10
Oranges 5
R2 Apricots 15
[code]....
The data are in a string. Columns are tab-delimited, lines are CRLF-delimited. Output should be the same. Multiple values are comma-delimited.
Here's the "flattened" output desired:
R1 Pears 10
R1 Apples 10
R1 Bananas 10
[code]....
Each column is filled down where there are blanks, and columns with multiple values (comma-delimited) are duplicated and filled down in the other columns.Complicating assumption: the number of columns in the input is arbitrary.Simplifying assumption: only one column will be comma-delimited.I'm working through this with a relatively naive solution (loops and some recursion), but I'd love to see if this is a situation where LINQ or some other solution would be more appropriate.I'm working in VB.NET at the moment, but C# is fine too.
Public Shared Function FlattenPlainTextTable(ByVal InputTable As String) As String
Const RowDelimiter As String = vbCRLF
Const ColDelimiter As String = vbTab
[code]....
View 1 Replies
ADVERTISEMENT
Feb 15, 2008
I am trying to import tab delimited text file to access table. The below code WORKS very well for csv file. when I try the same for tab delimited text it it importing all the fields into one column of the access table and combining the header files as one column.
View 8 Replies
Sep 28, 2009
I have a VB project that reads data from SQL as well as pulls data from other files into SQL. I need to import a text file into SQL table; there is no headers in the file and table in SQL is already exists. I found a code in Forum and slightly modified it, but I'm still missing something. I'm not sure how to specify that the text file is delimited with "|" character? Also - since there is no headers - not sure how to import it into correct columns? On the code below I'm getting "Insert" syntax error; on prior version of the code I was getting "field F1 doesn't exist".
[Code]...
View 12 Replies
Sep 4, 2009
I've created a table in SQL and inserted specific information into it programmatically. Now I need to export this whole table data, including headers into txt file (tab delimited)- from within Visual Basic project (Windows type application).
View 11 Replies
Feb 4, 2010
how to flatten an XML file to CSV format. I have worked on this all day today, and although I've found some simple examples, my issue is slighlty different. Please see example below...
[code]...
Notice that the data node has several different structure of child node. Is it possible to write XSL to flatten this?Second question is it possible to write the XSL in a generic way so that it doesn't need to be aware of node names and therefore just works for any XML fed in?
View 3 Replies
Oct 28, 2009
I have been trying to flatten the columns header the same way as we do it with buttons using .FlatStyle = FlatStyle.Flat but have been unsuccessful.
View 1 Replies
Nov 4, 2009
I'm trying to import a tab delimited file into a table.The issue is, SOMETIMES, the file will include an awkward record that has two "null values" and causes my program to throw a "unexpected end of file".For example, each record will have 20 fields. But the last record will have only two fields (two null values), and hence, unexpected EOF.
Currently I'm using a StreamReader. I've tried counting the lines and telling bcp to stop reading before the "phantom nulls", but StreamReader gets an incorrect count of lines due to the "phantom nulls".I've tried the following code to get rid of all bogus code (code borrowed off the net). But it just replaces the fields with empty spaces (I'd like the result of no line left behind).
Public Sub RemoveBlankRowsFromCVSFile2(ByVal filepath As String)
If filepath = DBNull.Value.ToString() Or filepath.Length = 0 Then Throw New ArgumentNullException("filepath")
If (File.Exists(filepath) = False) Then Throw New FileNotFoundException("Could not find CSV file.", filepath)
[code]....
I've tried using SSIS, but it encounters the EOF unexpected error.
View 5 Replies
May 7, 2011
So i'm new to working with vba in access and i'm having trouble getting this code to work. What it is suppose to do is take a selected text file and read the original file into a list box. Then there is a second button that when pressed will convert the text file from a pipe delimited file into a tab delimited file and then show the changed file into a new listbox.
Option Compare Database
Option Explicit
Function GetFilenameFromPath(ByVal strPath As String) As String
[code].....
View 2 Replies
Feb 11, 2011
how to make a text conversion like the below pic -
1. the textbox 1 is multiline with an array of comma delimited and tab delimited text.
2. the textbox 2 is one line textbox
3. A,B,C,D,E,F,G is where the position of each string before / after conversion
View 5 Replies
Apr 16, 2012
I have the following object:
countDictionary As Dictionary(of Category, Dictionary(of Date, Integer))
The Class has a Enumeration Property. For the purposes of demonstration, I'll call it MasterCategory.I have been trying to get out an object that looks like the following:
groupedCountDictionary As Dictionary(of MasterCategory, Dictionary(of Date, Integer)
The best result I could get was:
Lookup(of MasterCategory, Dictionary(of Date, Integer))
From:
countDictionary.ToLookup(Function(o) o.Key.MasterCategory, Function(o) o.Value)
Which results in a IEnumerable (Of Dictionary(of Date, Integer)) for each MasterCategory value.However, I need that IEnumerable of Dictionary flattened to one dictionary with all the integers summed (total counts) for each date. I then tried to use various selects and group bys (from numerous stackoverflow posts) to "flatten" it, but my efforts have fallen short.
Current Code
[Category Class]
- MasterCategory As Enum
- Name As String etc
[code]....
View 1 Replies
Mar 19, 2009
We have a nested XML data set but want it displayed on a single datagrid - i.e. no expand/hide functionality.
generically flatten any XML into a single table?
View 1 Replies
Apr 10, 2009
I have a text file that is in a comma separated format, delimited by " on most fields. I am trying to get that into something I can enumerate through (Generic Collection, for example). I don't have control over how the file is output nor the character it uses for the delimiter.
[Code]...
View 12 Replies
Jun 15, 2012
I would like to know a way (of course I want to know the best way) to edit a csv file, preferably without having to read the original and write the results to a new one.
Example 1: I want to split the contents of a column into two columns 123abc into 123, abc
Example 2: I want to remove letters from the beginning of a column AB123 into 123
Clarification: I do not need help solving my examples; I just need to be pointed in the right direction of reading and editing the same file.Here is a code example of using a temporary file that seems to me is too slow
Dim currentLine as string()
tempFile.AutoFlush = True
Do Until origFile.EndOfData
[code]....
View 2 Replies
Mar 29, 2010
I've created a program that will allow me to save user input into a text file on my C: drive. I'm trying to figure out a way where the text will be saved using a tab delimiter (I'm still new to Visual Basic.NET 2003). What I have to do is get the information to save in the form of columns and rows so that when the text file is opened using Microsoft Excel (ex. Microsoft Excel 2007), all of the information will be saved in its own columns and rows without any manual intervention (ex. no changing column widths or anything like that).
Here is my code so far.
HTML
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
[code]....
When opening the text file using Microsoft Excel, Excel should display the data like this:
Name | Mailing Address | Age
John | 2300 J. Street | 19
modify my code (so that the information will be using a tab delimiter) whereas the text can open up in the right rows and columns in Excel after a user has submitted the data to the text file.
View 2 Replies
Jul 8, 2011
I want to add delimited text/Data to Datagridview but i am unable to do. Till now i did the following codings
Dim ds As New DataSet
Dim strConnString As String
Dim dirCSV As String
[code]....
View 2 Replies
Dec 4, 2009
I am back, this time may very well be my last. But I will be using this as a great resource if I ever have to do this for work purposes. So far this is what I have. It works. But it doesn't look like the Lab9 specs and test plan. (Attached as .PDF's) Am I just crazy or is there some formatting that I need to do? reservations.jpg is a screen shot of what the output looks like. The UML diagram and specs.pdf should be what is output? Is that what I read? .PDF's if you want to know what is required** I can not explain it very well. Those documents will show exactly what is expected. What I have going out looks like the .jpg I have shown.
Private Sub frmReservations_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
[Code]...
View 2 Replies
Jul 19, 2006
I am working with a database that is used for managing program requirements ie text fields of varying lengths. The database program is capable of exporting data out in the form of tab and comma delimited files. I would like to see an example in vb.net 2003 of how I might be able to loop thru an entire delimited text file line by line isolating each tab delimited field to say a text box or a string variable.My ultimate goal is to be able to write this delimited data to a special spreadsheet application capable of dealing with text block sizes beyond the capability of Excel. So now I am interested in how to manipulate huge delimited text files in vb.net 03.
View 4 Replies
Aug 3, 2011
this is how i would normally import a comma delimited text file into a ms access table:
sql.CommandText = "SELECT * INTO [tblMailList] FROM [Text;DATABASE=" & System.IO.Path.GetDirectoryName(Me.OpenFileDialog1.FileName) & ";HDR=NO].[" & Me.OpenFileDialog1.SafeFileName & "]"
sql.ExecuteNonQuery()
my question is, how can i do something similar for a tab delimited text file?
View 8 Replies
Mar 5, 2010
I was wondering if anyone had a better(faster) way to parse a Tab delimited file in VB.net. need to read a file into an SQL table and the file contains about 300,000+ rows
Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(FilePath)
MyReader.TextFieldType = FileIO.FieldType.Delimited
MyReader.SetDelimiters(vbTab)
[code].....
View 2 Replies
Aug 30, 2009
I want to add a new table in my accdb and copy the content tfrom an existing table into the new one.
Is there an easy way to do this?
View 8 Replies
Dec 21, 2011
I have a data table whose one column is related to a column of another table. I have a listbox in a form which shows a column (which is sorted by another column value by ORDER clause) of the parent table and other columns are in textboxes. The child table is represented by a datagrid. When I add a new item in parent table and click save, the newly created item is listed at the bottom of the listbox violating my ORDER clause. When I wrote some codes to fill data again after updating, it shows an error message[code]...
View 3 Replies
Apr 23, 2009
There is way to input comma delimited files:
Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(currentfile)
MyReader.TextFieldType = FileIO.FieldType.Delimited
MyReader.SetDelimiters(",")
Is there an equivalent way to output a comma delimited file. The approach I used in earlier versions of Visual Basic does not work in .NET.
I have been using:
Printline(1, val1, val2, val3)
In .NET that is not comma delimiting
For now I am hardcoding ... & ", " & ... between variables.
View 4 Replies
Jan 9, 2011
I have spent a long time debugging an application that occasionally goes awry, it ends up that the problem is that the comma delimited text files that I am looping through occasionally inserts a comma within one of the fields.
At any rate, I was loading the text file into Excel to try and spot the error which was of no help, somehow Excel parsed out the field correctly and didn't use that extra comma as a delimiter. Is there any way I can get my application to do the same?
And example field value is: "7-8-ALL (1,2&3)", but of course my split code turns this into two separate array values.
View 4 Replies
Apr 26, 2009
I have a tab delimited text file as so :
name loan period loan amount
John 5 years 6000
Sarah 5 years 6000
Jane 1 month 100
I'm looking to copy the lines where "loan period" = "5 years" to where "loan period" = "1 month", in order to show the comparison. The new lines would be appended at the end of the resulting file. The ultimate end result I hope to achieve is this :
name loan period loan amount
John 5 years 6000
Sarah 5 years 6000
Jane 1 month 100
John 1 month 100
Sarah 1 month 100
I've been toying about this with Visual Basic .Net, and so far, this is what I've come up with
Dim strData As String
Dim i As Short
Dim strLine() As String
lngSize = 0
FileOpen(1, txtloanlistinput.Text, OpenMode.Input)
[Code] .....
View 2 Replies
Jan 18, 2011
Currently I am building an application that will do Data Validation on an Excel Spreadsheet.The application will allow a user to load an Excel Spreadsheet in and runs checks on the data. The application needs to export the spreadsheet as a pipe delimited file with all of the fields separated by the character.Currently I have written an Excel Macro that will export it as a Text File then delimit it and save the text file as a .ps1 file. I cant seem to figure out how to write this same code in VB instead of VBA.I am currently using Visual Studio 2010.Here is the code for my Macros that work fine but I need it in the application and not on the spreadsheet.
[Code]...
View 2 Replies
Mar 29, 2010
I was wondering if anyone had a better(faster) way to parse a Tab delimited file in VB.net. I need to read a file into an SQL table and the file contains about 300,000+ rows. Takes quiet a while. at the moment i have the below [Code].
View 6 Replies
Jan 20, 2008
I am relatively new to VB. I was initially using fileopen, writeline, and fileclose to create, write to, and close a comma delimited file. But recently I attended a training course where the instructor mentioned that these old methods will be going away and I should try and use Streamwriter instead. I was successful in converting some of my code to StreamWriter when creating a simple text file using WriteLine.
For example: myStreamWriter.WriteLine(text). But this writes an entire line of text.
I cannot see to find any information on how to create a comma-delimited file using StreamWriter. My old code was something like this: Writeline(filenumber, field1, field2, field3, field4). How to do the same thing with StreamWriter?
View 7 Replies
Feb 2, 2010
I m trying to manipulate some excel sheet data in visual basic but for tht i hv converted the excel file into text tab delimited file. now i m able access each column n row value as i want but the prob is the text file has to b input n the no of columns in it could be varied so i need a method to count the number of elements in th tab delimited array wen first time the text file line is read.
View 8 Replies
Jun 23, 2010
I have a text file with ";" as a delimter but I am not able to seperate the text, I am able to read one line at a time using ReadLine Function but I can not find a way to separate columns from this line.
View 4 Replies
May 18, 2010
I was trying to read Quote-delimited file using VB TextFieldParser. It doesn't work. However it seems to work if I set the delimiter as comma (","). How do I read quote delimited file?
Imports System.IO
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
[Code].....
View 2 Replies
