excel vba - Empty cell vs empty string in imported file -


in short, i'm importing csv file excel sheet , able tell difference between cell that's in csv no data, , cell isn't in csv.

for example, let following text in file "test.csv"

1,2,3,4 5,,7, 9,10 13,14,15,16 

then, in vba macro, load file query table (code largely copied http://www.zerrtech.com/content/excel-vba-open-csv-file-and-import):

with sheet1.querytables.add(connection:="text;test.csv", _   destination:=sheet1.range("a1"))     .name = "table1"     .fieldnames = true     .rownumbers = false     .filladjacentformulas = false     .preserveformatting = true     .refreshonfileopen = false     .refreshstyle = xloverwritecells     .savepassword = false     .savedata = true     .adjustcolumnwidth = true     .refreshperiod = 0     .textfilepromptonrefresh = false     .textfileplatform = 437     .textfilestartrow = 1     .textfileparsetype = xldelimited     .textfiletextqualifier = xltextqualifierdoublequote     .textfileconsecutivedelimiter = false     .textfiletabdelimiter = false     .textfilesemicolondelimiter = false     .textfilecommadelimiter = true     .textfilespacedelimiter = false     .refresh backgroundquery:=false end 

which loads csv cells follows:

   |   |  b  |  c  |  d  | ---+-----+-----+-----+-----+  1 |  1  |  2  |  3  |  4  | ---+-----+-----+-----+-----+  2 |  5  |     |  7  |     | ---+-----+-----+-----+-----+  3 |  9  |  10 |     |     | ---+-----+-----+-----+-----+  4 |  13 |  14 |  15 |  16 | ---+-----+-----+-----+-----+ 

so, question how can tell difference between empty cells b2 , d2 (which defined empty in csv) compared empty cells c3 , d3 (which aren't defined in csv)? if there isn't way loaded query table, there different method of importing csv accommodate this?

there might easier ways determine total imported values in each line, now:

option explicit  public sub csvcounter()     const file_name string = "c:\empty.csv"     const delim     string = ","      dim fso object, txt object, dat string, long, j long, x long     dim lns long, itms long, itm variant, v1 variant, v2 variant      set fso = createobject("scripting.filesystemobject")     set txt = fso.opentextfile(file_name)     dat = txt.readall      if instr(dat, delim) > 0 , instr(dat, vbcrlf) > 0         v1 = split(dat, vbcrlf)                                 'split lines         lns = ubound(v1) + 1         redim v2(1 lns, 1 ubound(split(v1(0), delim)))    'setup final range (array)          = 1 lns                                        'parse each line             itm = split(v1(i - 1), delim)                       'split delimiter             itms = ubound(itm) + 1             if x < itms                 x = itms                 redim preserve v2(1 lns, 1 x + 1)         'resize range fit             end if             j = 1 itms                 v2(i, j) = itm(j - 1)                           'copy values             next             v2(i, x + 1) = "total:   " & itms                   'show count         next         sheet1.range("a1").resize(lns, x + 1)             .value2 = v2             .entirecolumn.autofit         end     end if end sub 

empty cell vs empty string

(after importing csv, might late distinguish them)


Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -