473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How do I split the below string into multiple columns?

FAC00000000000000001000100000000007307803FIRECFF-F0337748-39 1027032015MYR00000000500000000+00000000500000000+3 0112013012301120140000Y 00000000000001000+00000000999000000000AAAA 00000000000000000+00000000FUMIGAWA MOTOR SDN BHD N000Y000000001350000011#


The above string is in single cell.

Excel macro works as follows but I want to split in access and not in excel. With help of excel macro recorder the following code was achieved but if the same splitting has to be done in access what will be the process?

Expand|Select|Wrap|Line Numbers
  1. Sub INSUDATAFILE()
  2. '
  3. ' INSUDATAFILE Macro
  4. ' INSUDATAFILE
  5. '
  6.  
  7. '
  8. Columns("A:A").Select
  9. Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
  10. FieldInfo:=Array(Array(0, 2), Array(3, 2), Array(20, 2), Array(24, 2), Array(41, 2), Array(45, 2), Array(75, 2), Array(77, 2), Array(85, 2), Array(88, 2), Array(106, 2), Array(124, 2), Array(132, 2), Array(135, 2), Array(143, 2), Array(147, 2), Array(148, 2), Array(198, 2), Array(216, 2), Array(224, 2), Array(227, 2), Array(236, 2), Array(266, 2), Array(284, 2), Array(292, 2), Array(352, 2), Array(353, 2), Array(356, 2), Array(357, 2), Array(374, 2), Array(375, 2), Array(376, 2)), TrailingMinusNumbers:=True
  11. End Sub
Attached Files
File Type: xls dummy.xls (26.5 KB, 327 views)
May 6 '15 #1
8 2525
Seth Schrock
2,965 Expert 2GB
Assuming that this is a fixed width style file, then you can use the Left(), Mid() and Right() functions to split the file into multiple parts.
May 6 '15 #2
zmbd
5,501 Expert Mod 4TB
What is the criteria you are using to split the string?
Split() function may be an option depending on the answer that question.
May 7 '15 #3
NeoPa
32,556 Expert Mod 16PB
I suggest you look into Import/Export specifications. They allow you to take in fixed length data and assign it to fields according to their positions.
May 7 '15 #4
Thanks NeoPa, I tried that option but the only pain is that the end result of this splitting contains more than 300 columns. So manually using line breaks after importing the file is a tedious task. Is there any way I can automate the process?

Thanks
May 7 '15 #5
zmbd
5,501 Expert Mod 4TB
+ Why not answer my question and tell us your criteria for splitting the string... you should also provide an example of the parsed string.

+ Many of us do not have the time nor resources to parse your code and attempt to figure out what you are attempting to do.
May 7 '15 #6
My sincere apologies ZMBD.

Criteria for splitting the string is - 1) no delimiter or space 2) string length will vary

Raw file data sample -
Expand|Select|Wrap|Line Numbers
  1. FAC00000000000000001000100000000007307803FIRECFF-F0337748-39 1027032015MYR00000000500000000+00000000500000000+3 0112013012301120140000Y 00000000000001000+00000000999000000000AAAA 00000000000000000+00000000FUMIGAWA MOTOR SDN BHD N000Y000000001350000011#
Parsed sample -
Expand|Select|Wrap|Line Numbers
  1. FAC    
  2. 00000000000000001    
  3. 0001    
  4. 00000000007307803    
  5. FIRE    
  6. CFF-F0337748-39    
  7. 10    
  8. 27032015    
  9. MYR    
  10. 00000000500000000+    
  11. 00000000500000000+    
  12. 30112013    
  13. 012    
  14. 30112014    
  15. 0000    
  16. Y        
  17. 00000000000001000+    
  18. 00000000    
  19. 999    
  20. 000000000    
  21. AAAA    
  22. 00000000000000000+    
  23. 00000000    
  24. FUMIGAWA MOTOR SDN BHD    <<Assuming on one line?>>
  25. N    
  26. 000    
  27. Y    
  28. 00000000135000001    
  29. 1    
  30. #
  31.  
  32.  
Thanks,
May 7 '15 #7
Rabbit
12,516 Expert Mod 8TB
So basically it's a fixed width like Seth mentioned in post #2. You're going to have to use either his method or NeoPa's method. There's no automatic method to do it, you will have to input the 300 column definitions. Since it's fixed width, there's nothing to tell the computer where the columns begin and end. Only you know that.
May 7 '15 #8
NeoPa
32,556 Expert Mod 16PB
Seraphic25:
Criteria for splitting the string is - 1) no delimiter or space 2) string length will vary
Unless you mean something other than what you say there is no specification for determining what goes where. This is unlikely to be the case so I can only assume you've left out a critical part of the equation. As this is, as I say, critical, it's very hard to give meaningful advice.

I suggest that you consider exactly what it is you're having to deal with and express that clearly and accurately in here as a first step. When we have that then we will be in a position to help you, which we'd like to be able to do.
May 8 '15 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: sri_san | last post by:
Hello, I have a datagrid in which the header needs to span over 2 columns. I have tried creating a tableCells and tableRow at runtime and set the columnspan property of a cell to 2. But, the...
4
by: Crirus | last post by:
There is a function somewhere to split a string with multiple tokens at a time? Say I have this: aaaa#bbbbb*ccccc$dddd I whould like to split it so the result whould be aaaa bbb
2
by: Mad Scientist Jr | last post by:
If I try splitting a string into an arraylist Dim arrList As New ArrayList arrList = Split("a,b,c", ",") I get this error: Value of type '1-dimensional array of String' cannot be converted to...
2
by: scratchadere | last post by:
I am tryin to partition a listbox into multiple columns.Here is the real problem.I am getting data from MS ACCESS database.I want to display it juz like how an email account displays unread messages...
1
by: Hetal | last post by:
Hi... We are on Visual Basic .NET 2003 and i am using DataView to sort the DataTable. However, we are facing problems with sorting the data based on multiple columns. The below statement to...
1
by: arulkumara | last post by:
Hi, I have one problem in mysql. how to split single row into multiple columns in mysql. my table structure: id salaryhigh salarymedium salarylow...
5
by: chinni0719 | last post by:
Hi I need to split the string which looks like n.col1 , b.col1 , n.col2 , b.col2, n.col3 , b.col3 , b.col4 , n.col4 , b.col5,n.col5 n.col1,n.col2 are columns these are present in...
0
by: John | last post by:
Hi I have written a function to split a string into sub strings of a given fixed max length. This is useful for example in breaking a long message into multiple strings of up to 160 characters...
3
by: Will | last post by:
Can someone help with code to delete multiple columns from an excel spreadsheet? I know which columns I need to delete. The code below will delete a single column but I'm not sure how to delete...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.